Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

Oracle External Table Basics

with 3 comments


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

Advertisements

Written by maclochlainn

June 19, 2008 at 7:27 am

3 Responses

Subscribe to comments with RSS.

  1. […] to Oracle External File Basics Posted in Oracle by maclochlainn on June 28th, 2008 While an earlier entry discusses CSV uploads to Oracle external tables, I neglected to mention some things. You can also […]

  2. I am trying to write the data from an internal table to a flat file and get the following error
    “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 “identifier”: expecting one of: “logfile, nologfile, version” etc..
    KUP-01008: the bad identifier was: RECORDS
    KUP-01007: at line 1 column 1
    ORA-06512: at “SYS.ORACLE_DATAPUMP”, line 19″

    What should be done?

    Tabitha Reji

    November 10, 2009 at 8:59 pm

  3. I don’t come here too often anymore, the new blog is where I check for questions.

    This error explains you’re using a keyword, RECORDS, please post the code for me to check on the newer blog. The entry for this there is here.

    maclochlainn

    December 3, 2009 at 6:07 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: