[oracle] sqlplus error on select from external table: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

I have setup a simple Oracle external table test that I (alongside a DBA and Unix admin) can't get to work.

The following is based on Oracle's External Tables Concepts. The database we're using is 11g.

This is the external table definition:

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 am able to run the script that creates "emp_load" with no issues. I can also describe the table fine. When I attempt "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 on the directory.

EDIT 2
I was able to get passed this error by 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 had to set the permissions on "EMP.BAD", "EMP.LOG" & "external_table_test2.dat" to 777 in order to get it to work. The oracle user doesn't own those files but is in the same group as the files are.

Any idea why I can't get this to work when I set the permissions on those files to 770? Again, oracle is in the same group as those files, so I figured that 770 would be OK for permissions...

When you want to create an external_table, all field's name must be written in UPPERCASE.

Done.

Keep in mind that it's the user that is running the oracle database that must have write permissions to the /defaultdir directory, not the user logged into oracle. Typically you're running the database as the user "Oracle". It's not the same user (necessarily) that you created the external table with.

Check your directory permissions, too.

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 2 nodes. After giving write permission on the log directory, on both sides, everything worked fine.

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