Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

Creating an external table that uses Oracle Data Pump

with 3 comments

External files are read-write when you use Oracle Data Pump. They are also stored in a proprietary format that includes some XML. There are a couple tricks to reading and writing through Oracle Data Pump. This example creates two scheme (user accounts). One schema is an exporter and the other is an importer. While they’re in one database instance, typically they’d exist in different database instances. This example uses a table created from the code in the Oracle Database 11g PL/SQL Programming book. You can download the code from the McGraw-Hill web site.

You must first create a virtual directory and then grant a schema privileges to read and write to the virtual directory. Like the related article on using external tables with SQL*Loader, this program reuses the same virtual and physical directories.

1. Create the virtual directory as a privileged user:

SQL> CREATE DIRECTORY download AS ‘C:\Download’

2. As the privileged user grant read-write privilege to the schema that writes the data:

SQL> GRANT READ, WRITE ON DIRECTORY download TO exporter;

3. Create an external table that acts as the target for exports from Oracle Data Pump The following creates an export table:

CREATE TABLE item_export
ORGANIZATION EXTERNAL
( TYPE oracle_datapump
DEFAULT DIRECTORY download
LOCATION ('item_export.dmp')
) AS SELECT item_id
, item_barcode
, item_type
, item_title
, item_subtitle
, item_rating
, item_rating_agency
, item_release_date
, created_by
, creation_date
, last_updated_by
, last_update_date
FROM item;

You should note that the SELECT clause must enumerate columns. If you attempt to use an *, you’ll raise the following exception:

SQL> CREATE TABLE item_export
2 ORGANIZATION EXTERNAL
3 ( TYPE oracle_datapump
4 DEFAULT DIRECTORY download
5 LOCATION ('item_export.csv')
6 ) AS SELECT * FROM item;
) AS SELECT * FROM item
*
ERROR at line 6:
ORA-30656: column type not supported on external organized table

4. As the privileged user grant read-write privilege to the schema that reads the data:

SQL> GRANT READ, WRITE ON DIRECTORY download TO importer;

5. Create an external table that acts as a source for imports through Oracle Data Pump. The following creates an import table:

CREATE TABLE item_import
( item_id NUMBER
, item_barcode VARCHAR2(20)
, item_type NUMBER
, item_title VARCHAR2(60)
, item_subtitle VARCHAR2(60)
, item_rating VARCHAR2(8)
, item_rating_agency VARCHAR2(4)
, item_release_date DATE
, created_by NUMBER
, creation_date DATE
, last_updated_by NUMBER
, last_update_date DATE)
ORGANIZATION EXTERNAL
( TYPE oracle_datapump
DEFAULT DIRECTORY download
LOCATION ('item_export.dmp'));

You should note that there’s a role reversal in declaring the column values. When an external table is importing through Oracle Data Pump, you must explicitly define the column names, and data types.

If you drop and recreate an export external table, you must first remove the referenced file. You’ll get the following error when you forget:

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

You may also want to review this article. It shows you how to check if the external file is there before you query the data.

Written by maclochlainn

June 19, 2008 at 6:55 am

3 Responses

Subscribe to comments with RSS.

  1. […] 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 […]

  2. […] Creating an external table that uses Oracle Data Pump […]

  3. Im tryin to transfer data between two dbs through dbms_file_transfer. I have the dump file on the destination db, and when I try to mount an external table from the .dmp file, I get an error KUP:11018: file not a valid dump file.

    Im not mounting as external tables on source side. I just export the data in to .dmp file on the source side and sent it across to destination db.

    Bhadri

    January 27, 2011 at 11:07 pm


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: