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

Question

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...

Answer 1

Our version of Oracle is running on Red Hat Enterprise Linux. We experimented with several different types of group permissions to no avail. The /defaultdir directory had a group that was a secondary group for the oracle user. When we updated the /defaultdir directory to have a group of "oinstall" (oracle's primary group), I was able to select from the external tables underneath that directory with no problem.

So, for others that come along and might have this issue, make the directory have oracle's primary group as the group and it might resolve it for you as it did us. We were able to set the permissions to 770 on the directory and files and selecting on the external tables works fine now.