ORA-29913: Error in Executing ODCIEXTTABLEOPEN Callout (Doc ID 458620.1)

In this Document

APPLIES TO:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 12-Mar-2018***

SYMPTOMS

To reproduce the issue:

create directory meta_data as '/home/oracle/customer/meta_data';

			grant read, write on directory meta_data to cartl01;

The foo.dat file was placed in the meta_data directory at the OS level
In this case the file only contained a single line with number '100'.

Create the next external table:

CREATE TABLE FOO

			(

			  NUM  NUMBER(10)

			)

			ORGANIZATION EXTERNAL

			  (  TYPE ORACLE_LOADER

			     DEFAULT DIRECTORY META_DATA

			     ACCESS PARAMETERS

			       ( records delimited by newline

			    NOLOGFILE  )

			     LOCATION ('foo.dat')

			  )

			REJECT LIMIT 0

			NOPARALLEL

			NOMONITORING;

Select from the external table encounters the error:

SQL> show user

			USER is "CARTL01"

			SQL> select * from foo;

			select * from foo

			*

			ERROR at line 1:

			ORA-29913: error in executing ODCIEXTTABLEOPEN callout

			ORA-29400: data cartridge error

			KUP-04040: file foo.dat in META_DATA not found

			ORA-06512: at "SYS.ORACLE_LOADER", line 19

NOTE:
With 11.2, the last line in the mentioned error stack was no longer observed.

CAUSE

Privileges at the OS level show that the meta_data directory had only rwx privileges for one user:

> ls -ltr meta_data

			drwx------ 2 user1 xyz 4096 Sep 13 17:00 meta_data

Even though the file permissions were set to 777, the Oracle user could not access the file because permissions at the directory level would not allow it.

> ls -ltr meta_data

			-rwxrwxrwx 1 user1 xyz    4 Sep 13 17:28 foo.dat

SOLUTION

Change permissions on the directory to allow the Oracle user to read and write from it:

> ls -ltr meta_data

			drwxrwxrwx 2 user1 xyz 4096 Sep 13 17:38 meta_data

Selecting from the external table no longer encounters an error:

SQL> select * from foo;

			NUM

			----------

			       100

NOTE:
The level of permissions to set for the directory is purely a business decision.
In this example, the directory permission are set to 777 which may not be prudent in all environments.