MacLochlainns Weblog

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.