MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

SQL*Loader external tables

with 7 comments

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 (and don’t forget to create the physical directory on the file system):

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;

You may benefit from downloading and installing the VERTICAL_QUERY function. It lets you display information for directory permissions easily without formatting at the SQL*Plus command line. You’ll find all you need in this post with the source code as open text.

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 as the IMPORTER user (or whichever one you choose):

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).

1  Apple               1.49
2  Orange              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 19th, 2008 at 6:09 am

Posted in Uncategorized

7 Responses to 'SQL*Loader external tables'

Subscribe to comments with RSS or TrackBack to 'SQL*Loader external tables'.

  1. As a side note, keep your CSV files in the directory you create with the CREATE DIRECTORY … command. The CREATE TABLE … statement will likely be in an SQL file, but you put that into the BIN dir (or another place).

    Karsten

    10 Feb 09 at 5:18 am

  2. I need your help. When i created a external table all is ok. But when I run a select return the next error:
    ORA-29913: error in executing ODCIEXTTABLEOPEN callout
    ORA-29400: data cartridge error
    KUP-04040: file TEMP.LST in EXT_DIR not found
    ORA-06512: at “SYS.ORACLE_LOADER”, line 14
    ORA-06512: at line 1

    Can you help me?

    Thanks.

    Luis Isunza

    11 May 09 at 4:17 pm

  3. I can replicate your problem if I change the ownership of the directory to something other than the Oracle database OS user’s account and restrict both group and user read access. Here’s the error I got and another post about discovering the file.

    SQL> SELECT * FROM CHARACTER;
    SELECT * FROM CHARACTER
    *
    ERROR at line 1:
    ORA-29913: error IN executing ODCIEXTTABLEOPEN callout
    ORA-29400: DATA cartridge error
    KUP-04040: file CHARACTER.csv IN DOWNLOAD NOT found

    I’d suggest you change the directory permissions to 740 if you want to allow only read or 760 if you want to allow read and write. The other way I replicated it was to have no physical file present. That’s a different issue. I’ve added to the blog post to solve for it.

    Hope this helps.

    maclochlainn

    11 May 09 at 7:10 pm

  4. Luis, I finally got back to this and did it justice. I apologize for the delay. You can find it here.

    maclochlainn

    11 Jul 09 at 7:49 pm

  5. Thanks for your article. I need your advice inusing the function ‘replace’ in the access parameters of the External Table. I am replacing all the ‘\’ with a null value in column straatnaam_nen( “replace(:STRAATNAAM_NEN, ‘\’, ”)” )

    Appreciate your help,

    Kind Regards
    Donald

    Donald

    17 Jul 09 at 8:04 am

  6. Step 1 needs a ; at the end

    Ben

    3 Nov 09 at 2:05 pm

  7. Thanks, it’s fixed.

    maclochlainn

    15 Jun 10 at 11:23 pm

Leave a Reply