CODEWITHSUNDEEP
oracle-databasecsvplsql
Nikita Buriak
Nikita Buriak

Reputation: 75

ORA-29913: error in executing ODCIEXTTABLEOPEN callout when inserting csv into oracle

I'm trying to execute this code in PL/SQL:

create or replace directory ext_tab_dir as 'C:/mydir';
GRANT READ,WRITE ON DIRECTORY ext_tab_dir TO PUBLIC;
DROP TABLE emp_load;
CREATE TABLE emp_load (v1 VARCHAR2(4000), 
                        v2 VARCHAR2(4000) 
                        )
   ORGANIZATION EXTERNAL (
     TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
     ACCESS PARAMETERS (
     RECORDS DELIMITED BY NEWLINE
     BADFILE ext_tab_dir:'bad.bad'
     LOGFILE ext_tab_dir:'log.log'
     FIELDS TERMINATED BY ','         
     )
     LOCATION ('testfile.csv')
   ); 
--   INSERT INTO tablename(v1,v2)
   SELECT * From emp_load

and then getting next errors:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout    
ORA-29400: data cartridge error error opening file C:/mydir/log.log

I do get that it has to do something with permissions, but I'm the one who created that directory, so how do I grant priveleges to myself if it is set like this by default? Is there any way to perform that sort of operation from PL/SQL?

Upvotes: 2

Views: 86841

Answers (2)

Neo
Neo

Reputation: 135

You should grant all the privileges to the directories and files in the location pointing to the file you are trying to access.

eg : if the file you are trying to excess is in /home/dummy_folder/new_folder/file.txt then you should grant all the administrative privileges to dummy_folder, new folder and file.txt as well

Upvotes: 2

Rose
Rose

Reputation: 641

Try something like this.

GRANT SELECT, INSERT, UPDATE, DELETE ON emp_load TO NikitaBuriak;

Replace 'NikitaBuriak' with the ID you used when you created the table..

Upvotes: 3

Related Questions