How to resolve ORA-29913 with external tables



ORA-29913 is one of the common error when working with external tables in Oracle database. We would be looking at various ways the error can be produced and what can be done to resolve the error

  1. External File not found or permission incorrect
SQL> select * from Scott.example_external_table;
select * from example_external_table
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file test1.dat in TEST_DIR not found
ORA-06512: at “SYS.ORACLE_LOADER”, line 14
ORA-06512: at line 1

When analyzing the table, you get a similar message:

SQL> execute sys.dbms_stats.gather_table_stats(‘SCOTT’,’EXAMPLE_EXTERNAL_TABLE’);
BEGIN sys.dbms_stats.gather_table_stats(‘SCOTT’,’EXAMPLE_EXTERNAL_TABLE’); END;
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file test1.dat in TEST_DIR not found
ORA-06512: at “SYS.DBMS_STATS”, line 7161
ORA-06512: at “SYS.DBMS_STATS”, line 7174
ORA-06512: at line 1

Or in general way

ORA-20011: Approximate NDV failed:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file <file_name> in <directory_name> not found

 

Reason

The external files have been moved from the correct location

SQL> select * from dba_directories ;

OWNER DIRECTORY_NAME DIRECTORY_PATH
—— ————– ———————–
SYS TEST_DIR /u01/oradata/external_files

Check the File Names associated with external table

select * from dba_external_locations
2 where table_name=’EXAMPLE_EXTERNAL_TABLE’;

OWNER TABLE_NAME LOCATION DIRECTORY_OWNER DIRECTORY_NAME
—– ————- ——– ————— —————
SYS EXAMPLE_EXTERNAL_TABLE test1.dat SYS TEST_DIR
SYS EXAMPLE_EXTERNAL_TABLE test2.dat SYS TEST_DIR

Now checking at the OS level
$ cd /u01/oradata/external_files
$ ls test[1-2]/dat
No such file or directory

So files are not present at the correct location

Resolution:
Move back both the files to the original location

$ mv /u02/oradata/external_files/test2.dat /u01/oradata/external_files
$ mv /u02/oradata/external_files/test1.dat /u01/oradata/external_files

Now
Both the below statement will succeed

select * from Scott.example_external_table;

execute sys.dbms_stats.gather_table_stats(‘SCOTT’,’EXAMPLE_EXTERNAL_TABLE’);

2) Incorrect data format in external file

ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at “SYS.ORACLE_LOADER”, line 14
ORA-06512: at line 1

Reason

1) The external file may be having empty lines
2) Check the TAB delimiter or the HEX delimiter

a. The TAB delimiter ‘\t’, used by Oracle, is represented by a HEX value ’09’
b. The HEX value in the datafile should match the HEX value specified in the
CREATE statement
c. The HEX specification in the CREATE statement should look like 0X'<value&
gt;’ or 0x'<value>’
3) If the column added to the external table,same should be present in the external file

Resolution
Check the error in the log and correct the problem accordingly

3) Error with Null column

ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04043: table column not found in external source:

Reason
The external file does have null values for the last column which is originally a number column. When we change the null to a number 0,then the query succeeds.

Resolution
To load a NULL value,we need to include ENCLOSED BY delimiter in the table definition.

records delimited by newline
fields terminated by ‘,’
optionally enclosed by ‘ ‘ <<<=====
missing field values are null

4) ORA-29913 can also occur if Oracle is not able to create the log files also.So check for the permission of the directories

Here are some good information about External Table,You should  check out

What are EXTERNAL TABLES in Oracle?

1)External tables are read only tables where the data is stored in flat files outside the database

2) You can user external table feature to access external files as if they are tables inside the database.

3)   When you create an external table, you define its structure and location with in oracle. Basically you just store the metadata inside the oracle

4) When you query the table, oracle reads the external table and returns the results just as if the data had been stored with in the database.

5) The oracle server provides two major oracle driver to read the flat files

  1. a) Oracle_loader: This is used for the reading the flat files using the oracle loader technology. It basically allows to read the files which can be interpreted using sql loader technology
  2. b) Oracle_datapump: This is used for both importing and exporting of data using platform independent format

More can be found at the below link

EXTERNAL TABLES


Leave a Reply