Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

Creating an external table that uses SQL*Loader

with 3 comments

The updated version of this entry is here, with formatted, highlighted, and copiable code.

External files are read-only when you use SQL*Loader. You must use Oracle Data Pump when you want to make them read and write files. This other page shows you how to implement both read and write external files.

You must first create a virtual directory and then grant a schema privileges to read or to read and write to the virtual directory. If you don’t plan on having any log, bad record failure, or discard files, you can grant read only permissions on the virtual directory. You must grant read and write privileges when you also want to generate log and exception files.

1. Create the virtual directory as a privileged user:

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

2. As the privileged user grant the read only privilege to the schema that will read the data:

SQL> GRANT READ ON DIRECTORY download TO importer;

Alternatively, if you want to write log or exception files, grant read and write like:

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

3. Create Comma Separated Value (CSV) file in the target directory:

1,'Indiana','Jones'
2,'Ravenwood','Marion'
3,'Marcus','Brody'
4,'Rene','Belloq'

4. Create an External Table in the Database

Create an external table that uses the oracle_loader method to read a file. The following doesn’t require write privileges because it suppresses logging:

CREATE TABLE character
( character_id NUMBER
, first_name VARCHAR2(20)
, last_name VARCHAR2(20))
ORGANIZATION EXTERNAL
( TYPE oracle_loader
DEFAULT DIRECTORY download
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
NOBADFILE
NODISCARDFILE
NOLOGFILE
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY "'"
MISSING FIELD VALUES ARE NULL )
LOCATION ('character.csv'))
REJECT LIMIT 0;

You should note that the rejection limit is set to zero because logging is disabled. This is a good practice because you’re should be assuming an all or nothing on the load without the log information.

Create an external table that uses the SQL*Loader method to read a file. The following  requires write privileges because it log activity and errors:

CREATE TABLE character
( character_id NUMBER
, first_name VARCHAR2(20)
, last_name VARCHAR2(20))
ORGANIZATION EXTERNAL
( TYPE oracle_loader
DEFAULT DIRECTORY download
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE     character
DISCARDFILE character
LOGFILE     character
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY "'"
MISSING FIELD VALUES ARE NULL )
LOCATION ('character.csv'))
REJECT LIMIT UNLIMITED;

The rejection limit is unlimited because they’ll be captured in the enabled log files.

Unlike the ".csv" in the LOCATION argument, you can’t append a ".log", ".bad", or ".dis" extensions for log, bad, or discard files unless you enclose them in single quotes. You’ll raise an ugly exception if you include those exceptions when you query the table. Unfortunately, the syntax error doesn’t throw an exception when you create the table. Here’s an example of the runtime exception stack:

SELECT * FROM character
*
ERROR at line 1:
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 override the ".log", ".bad", or ".dis" extensions with the following syntax, which also states the virtual directory where they should be written. Please note that the virtual directory MUST BE IN UPPERCASE only!

CREATE TABLE character
( character_id NUMBER
, first_name VARCHAR2(20)
, last_name VARCHAR2(20))
ORGANIZATION EXTERNAL
( TYPE oracle_loader
DEFAULT DIRECTORY download
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE     'DOWNLOAD':'character.bad'
DISCARDFILE 'DOWNLOAD':'character.dis'
LOGFILE     'DOWNLOAD':'character.log'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY "'"
MISSING FIELD VALUES ARE NULL )
LOCATION ('character.csv'))
REJECT LIMIT UNLIMITED;

If you forget to make the virtual directory an uppercase string, you’ll raise the following error stack:

ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04080: directory object download not found

You can also use position specific files. There are two separate syntax patterns but only one is mentioned in the Oracle 11g Database Utilities manual (the first that uses CHAR(n) to designate length).

1Apple               1.49
2Orange              2

You could create a position specific file for the same data but it’s probably easier with a different file example. This example fruits and their price per pound, like this one:

This uses length of fields:

CREATE TABLE grocery
( grocery_id NUMBER
, item_name VARCHAR2(20)
, item_amount NUMBER(4,2))
ORGANIZATION EXTERNAL
( TYPE oracle_loader
DEFAULT DIRECTORY download
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'DOWNLOAD':'grocery.bad'
LOGFILE 'DOWNLOAD':'grocery.log'
FIELDS
MISSING FIELD VALUES ARE NULL
( grocery_id  char(3)
, item_name   char(20)
, item_amount char(4)))
LOCATION ('grocery.csv'))
REJECT LIMIT UNLIMITED;

The other approach is to label the starting and ending character positions. It’s more tedious because you need to keep track of both values. Also, don’t forget character positions are 1-based numbers.

This uses absolute start and end positions:

CREATE TABLE grocery
( grocery_id NUMBER
, item_name VARCHAR2(20)
, item_amount NUMBER(4,2))
ORGANIZATION EXTERNAL
( TYPE oracle_loader
DEFAULT DIRECTORY download
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'DOWNLOAD':'grocery.bad'
LOGFILE 'DOWNLOAD':'grocery.log'
FIELDS
MISSING FIELD VALUES ARE NULL
( grocery_id  position(1:3)
, item_name   position(4:23)
, item_amount position(24:27)))
LOCATION ('grocery.csv'))
REJECT LIMIT UNLIMITED;

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:09 am

3 Responses

Subscribe to comments with RSS.

  1. […] “Creating an external table that uses SQL*Loader” page demonstrates how you can create an input or read-only external table. The […]

  2. […] Creating an external table that uses SQL*Loader […]

  3. […] “Creating an external table that uses SQL*Loader” page demonstrates how you can create an input or read-only external table. The […]


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: