MacLochlainns Weblog

External files are great tools for reading data into and writing data out of an Oracle database. You have two options for reading data into the database. One uses SQL*Loader and the other uses Oracle Data Pump. You have only one option to write data from the database into an external table file. That’s Oracle Data Pump.

I thought this was pretty straightforward when recommending it as a solution. Given the questions that I got back, it appears that it isn’t. Actually, I couldn’t find an example for how you import data through an external table by using Oracle Data Pump. I only checked the Oracle Database Utilities 11g documentation, but maybe its somewhere else. 

The “Creating an external table that uses SQL*Loader” page demonstrates how you can create an input or read-only external table. The “Creating an external table that uses Oracle Data Pump” shows you how to create read-write external tables.

Exceptions covered in the Articles

The two referenced pages should help you understand the basics and resolve these error messages (at least on an Oracle Database 11g where I tested them):

Exception stack raised by Oracle SQL*Loader when you provide file extensions for log, bad, or discarded file names without enclosing them in single quotes:

ORA-29913: error IN executing ODCIEXTTABLEOPEN callout
ORA-29400: DATA cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "dot": expecting one OF: "badfile,
byteordermark, characterset, colon, column, data, delimited, discardfile,
disable_directory_link_check, fields, fixed, load, logfile, language,
nodiscardfile, nobadfile, nologfile, date_cache, processing, readsize, string,
skip, territory, vari"
KUP-01007: at line 2 COLUMN 20

You can also enclose a different Oracle virtual directory by using ‘virtual_directory’:’name.extension’ syntax.

Exception stack raised by Oracle Data Pump when you fail to enumerate columns in the source query:

ERROR at line 6:
ORA-30656: COLUMN TYPE NOT supported ON external organized TABLE

Exception stack raised by Oracle Data Pump when you try to rebuild the external table without previously dropping the external file:

CREATE TABLE item_export
*
ERROR at line 1:
ORA-29913: error IN executing ODCIEXTTABLEOPEN callout
ORA-29400: DATA cartridge error
KUP-11012: file item_export.dmp IN C:\DATA\Download already EXISTS