MacLochlainns Weblog

Oracle Database 9i introduced external tables. You can create external tables to load plain text files by using Oracle SQL*Loader. Alternatively, you can create external tables that load and unload files by using Oracle Data Pump. This article demonstrates both techniques.

You choose external tables that use Oracle SQL*Loader when you want to import plain text files. There are three types of plain text files. They are comma-separated value (CSV), tab-separated value (TSV), and position specific text files.

External tables that use Oracle Data Pump don’t work with plain text files. They work with an Oracle proprietary format. That means you load source files previously created by an Oracle Data Pump export. You typically create external tables with Oracle Data Pump when you’re moving large data sets between database instances.

External tables use Oracle’s virtual directories. An Oracle virtual directory is an internal reference in the data dictionary. A virtual directory maps a unique directory name to a physical directory on the local operating system. Virtual directories were simple before Oracle Database 12c gave us the multitenant architecture. In a multitenant database there are two types of virtual directories. One services the schemas of the Container Database (CDB) and it’s in the CDB’s SYS schema. The other services the schemas of a Pluggable Database (PDB) and it’s in the ADMIN schema for the PDB.

You can create a CDB virtual directory as SYSTEM user with the following syntax in Windows:

SQL> CREATE DIRECTORY upload AS 'C:\Data\Upload';

or, like this in Linux or Unix:

SQL> CREATE DIRECTORY upload AS '/u01/app/oracle';

There are some subtle differences between these two statements. Windows directories or folders start with a logical drive letter, like C:\, D:\, and so forth. Linux and Unix directories start with a mount point like /u01.

One of the subtle differences is directory and file ownership. You can change ownership for a directory in Windows as the Administrator account. The change makes the directory publically accessible, and that’s probably fine for a test database. After such a change, the Oracle user can find the external file even when parent directories aren’t navigable. Although, a production database on Windows would requires more skill at setting and restricting file permissions.

Linux and Unix directories require that the oracle user can navigate the tree from the mount point to the target physical directory. Also, you must designate the ownership of external files as the same as the Oracle Database user. Assuming a standard install of the Oracle Database 11g XE instance, you would issue the following shell command as the root user to change file ownership and access privileges:

# chown –R oracle:dba /u01/app/oracle/upload
# chmod –R 755 /u01/app/oracle/upload

After you create the virtual directory, you must grant privileges or a role to the user that defines the external table. While data and log files should be separated, this example assumes they co-exist in the same directory.

The following statement grants read privilege for the data file and write privileges for the log files to a CDB user. You should run this statement as the system user.

SQL> GRANT read, WRITE ON DIRECTORY upload TO c##importer;

or, like this in non-multitenant database or PDB user:

SQL> GRANT read, WRITE ON DIRECTORY upload TO importer;

The last preparation steps require a plain text file in the physical directory. Let’s create a CSV file of key Avenger characters, and name it the avenger.csv file.

The avenger.csv file holds the following values:

1,'Anthony','Stark','Iron Man'
2,'Thor','Odinson','God of Thunder'
3,'Steven','Rogers','Captain America'
4,'Bruce','Banner','Hulk'
5,'Clinton','Barton','Hawkeye'
6,'Natasha','Romanoff','Black Widow'

You create the external table after creating the virtual directory, granting read and write privileges on the virtual directory, and creating an external physical file. The syntax for the CREATE TABLE statement of an external table is very similar to the syntax of an ordinary table. The difference between the two types of tables is a clause. An internal table has a STORAGE clause, while an external table has an ORGANIZATION EXTERNAL clause.

The following creates the avenger table as an external table:

SQL> CREATE TABLE avenger
  2  ( avenger_id      NUMBER
  3  , first_name      VARCHAR2(20)
  4  , last_name       VARCHAR2(20)
  5  , character_name  VARCHAR2(20))
  6    ORGANIZATION EXTERNAL
  7    ( TYPE oracle_loader
  8      DEFAULT DIRECTORY upload
  9      ACCESS PARAMETERS
 10      ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
 11        BADFILE     'UPLOAD':'avenger.bad'
 12        DISCARDFILE 'UPLOAD':'avenger.dis'
 13        LOGFILE     'UPLOAD':'avenger.log'
 14        FIELDS TERMINATED BY ','
 15        OPTIONALLY ENCLOSED BY "'"
 16        MISSING FIELD VALUES ARE NULL)
 17      LOCATION ('avenger.csv'))
 18  REJECT LIMIT UNLIMITED;

Lines 1 through 5 create the columns of the avenger table. Lines 6 through 17 contain the ORGANIZATION EXTERNAL clause. Line 7 designates the external table as managed by the Oracle SQL*Loader utility. Line 8 sets the default virtual directory. Lines 11 through 12 set the bad, discard, and log file location. The bad and discard files keep all that can’t be read. The log file keeps all rows read by a query against the avenger table.

You also have the option of making all reads automatic parallel. You simply add a PARALLEL clause, like this:

19  PARALLEL;

A simple query with SQL*Plus formatting lets us test whether the avenger table works. The query to display all columns of all rows is:

SQL> COLUMN first_name FORMAT A10
SQL> COLUMN last_name  FORMAT A10
SQL> COLUMN character_name FORMAT A15
SQL> SELECT * FROM avenger;

Yields the following formatted output:

AVENGER_ID FIRST_NAME LAST_NAME  CHARACTER_NAME
---------- ---------- ---------- ---------------
         1 Anthony    Stark      Iron Man
         2 Thor       Odinson    God of Thunder
         3 Steven     Rogers     Captain America
         4 Bruce      Banner     Hulk
         5 Clinton    Barton     Hawkeye
         6 Natasha    Romanoff   Black Widow
 
6 rows selected.

It’s possible to redefine the avenger table to use either relative or fixed positional columns. You change the ACCESS PARAMETERS clause on lines 9 through 16 to make this change.
The following ACCESS PARAMETERS clause runs across lines 9 through 19 and creates relative position definition:

  9      ACCESS PARAMETERS
 10      ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
 11        BADFILE     'UPLOAD':'avenger.bad'
 12        DISCARDFILE 'UPLOAD':'avenger.dis'
 13        LOGFILE     'UPLOAD':'avenger.log'
 14        FIELDS
 15        MISSING FIELD VALUES ARE NULL
 16        ( avenger_id      CHAR(4)
 17        , first_name      CHAR(20)
 18        , last_name       CHAR(20)
 19        , character_name  CHAR(4)))

You can change from the relative position, to a fixed position by changing lines 16 through 19. The change for fixed length strings is:

 16        ( avenger_id      POSITION 1:4
 17        , first_name      POSITION 5:24
 18        , last_name       POSITION 25:44
 19        , character_name  POSITION 45:64))

Having worked with the Oracle SQL*Loader version of external tables, lets create one that uses Oracle Data Pump. Assuming we keep the same data structure, drop the avenger table, and create a catalog managed avenger_internal table.

This statement creates the avenger_internal table:

SQL> CREATE TABLE avenger_internal
  2  ( avenger_id      NUMBER
  3  , first_name      VARCHAR2(20)
  4  , last_name       VARCHAR2(20)
  5  , character_name  VARCHAR2(20));

To avoid writing six INSERT statements, you can write one INSERT statement with a query against the SQL*Loader avenger table. The syntax for that INSERT statement is:

SQL> INSERT INTO avenger_internal
  2  SELECT * FROM avenger;

With an internally managed table, you create an avenger_export table that uses Oracle Data Pump like this:

SQL> CREATE TABLE avenger_export
  2  ORGANIZATION EXTERNAL
  3  ( TYPE oracle_datapump
  4    DEFAULT DIRECTORY upload
  5    LOCATION ('avenger_export.dmp')) AS
  6  SELECT   avenger_id
  7  ,        first_name
  8  ,        last_name
  9  ,        character_name
 10  FROM     avenger_internal;

The CREATE TABLE statement exports data to the avenger_export.dmp file immediately. You must drop and recreate the avenger_export table to get a fresh extract of the avenger_internal table’s data. You must also remove the previous avenger_export.dmp file before you try to recreate the avenger_export table.

You raise the following error when you fail to remove the previous export file:

CREATE TABLE avenger_export
*
ERROR AT line 1:
ORA-29913: error IN executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11012: FILE avenger_export.dmp IN /u01/... already EXISTS

This is a simple example with only four columns. You might think you can use the SELECT * as the SELECT-list of the query on lines 6 through 10. If you’re running Oracle Database 12c, you can use the shorter syntax, but if you’re running Oracle Database 11g you can’t. If you attempt it in an Oracle Database 11g instance, the CREATE TABLE statement returns the following error:
ERROR at line 6:

ORA-30656: COLUMN TYPE NOT supported ON external organized TABLE

You create an avenger_import table with another twist on this now familiar Oracle SQL syntax. The CREATE TABLE statement is:

SQL> CREATE TABLE avenger_import
  2  ( avenger_id      NUMBER
  3  , first_name      VARCHAR2(20)
  4  , last_name       VARCHAR2(20)
  5  , character_name  VARCHAR2(20))
  6    ORGANIZATION EXTERNAL
  7    ( TYPE oracle_datapump
  8      DEFAULT DIRECTORY up2load
  9      LOCATION ('avenger_export.dmp'));

Like the export process, the import process happens immediately when the CREATE TABLE statement runs. A query against the avenger_import table would show you the original six rows we started with in the plain text files.

This article has introduced Oracle external tables. It has shown you how to import plain text files with SQL*Loader. It has also shown you how to export files from tables.