Sqlplus error when choosing from an external table: ORA-29913: error while executing statement ODCIEXTTABLEOPEN

Question

I installed a simple external Oracle external test, which I (along with the DBA and Unix administrator) cannot work.

The following describes Oracle Concepts on external tables . The database we use is 11 g.

This is the definition of the external table:

drop table emp_load; CREATE TABLE emp_load (employee_number CHAR(5), employee_dob DATE, employee_last_name CHAR(20), employee_first_name CHAR(15), employee_middle_name CHAR(15), employee_hire_date DATE) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY defaultdir ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS (employee_number CHAR(2), employee_dob CHAR(20), employee_last_name CHAR(18), employee_first_name CHAR(11), employee_middle_name CHAR(11), employee_hire_date CHAR(10) date_format DATE mask "mm/dd/yyyy" ) ) LOCATION ('external_table_test.dat') ); 

This is the contents of "external_table_test.dat":

 56november, 15, 1980 baker mary alice 09/01/2004 87december, 20, 1970 roper lisa marie 01/01/1999 

I can run a script that creates "emp_load" without any problems. I can also describe the table perfectly. When I try to "select * from emp_load", I get the following errors:

 SQL> select * from emp_load; select * from emp_load * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error error opening file /defaultdir/EMP_LOAD_29305.log 

EDIT 1
oracle has read / write permissions to the directory.

EDIT 2
I managed to pass this error using the following external table definition:

 CREATE TABLE emp_load (employee_number CHAR(3), employee_last_name CHAR(20), employee_middle_name CHAR(15), employee_first_name CHAR(15) ) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY defaultdir ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE BADFILE DHHSMAPSIS:'EMP.BAD' LOGFILE DHHSMAPSIS:'EMP.LOG' FIELDS TERMINATED BY ',' ) LOCATION ('external_table_test2.dat') ) REJECT LIMIT UNLIMITED; 

My .dat file looks like this:

 056,baker,beth,mary 057,smith,teddy,john 

I needed to set permissions on "EMP.BAD", "EMP.LOG" and "external_table_test2.dat" to 777 to make it work. The oracle user does not own these files, but is in the same group as the files.

Any idea why I can't get this to work when I set the permissions for these files to 770? Again, the oracle is in the same group as these files, so I decided 770 would be ok for permissions ...

Answer 1

Our version of Oracle runs on Red Hat Enterprise Linux. We experimented with several different types of group permissions to no avail. There was a group in the / defaultdir directory that was the secondary group for the oracle user. When we updated the / defaultdir directory to have the oinstall group (the primary oracle group), I was able to easily select from external tables under this directory.

So, for others who come and may have this problem, make the original oracle group in the directory as a group, and it can solve it for you, just like we do. We managed to set permissions to 770 in the directory and files, and the choice on external tables is now fine.

Answer 2

Keep in mind that this is the user who runs the oracle database, which must have write permissions to the / defaultdir directory, and not the user registered in oracle. Typically, you use the database as an Oracle user. This is not the same user (required) that you created the external table with.

Also check directory permissions.

Answer 3

We faced the same problem:

 ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error error opening file /fs01/app/rms01/external/logs/SH_EXT_TAB_VGAG_DELIV_SCHED.log 

In our case, we had a RAC with two nodes. After granting permission to write to the log directory on both sides, everything works fine.

Answer 4

We had this error in Oracle RAC 11g on Windows, and the solution was to create the same OS directory tree and external file on both nodes.

Answer 5

If you want to create an external table, the entire field name must be written in UPPERCASE.

Done.