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 gt  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   amp   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

User · Accepted Answer

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.

User · Answer

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

User · Answer

When you want to create an external table  all field s name must be written in UPPERCASE   Done

User · Answer

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

User · Answer

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

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

Examples related to oracle

Examples related to oracle11g