Database Tutorial

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.

Task #1

You must first create a virtual directory, which requires that you have also created a physical directory (or folder) on your file system. After you’ve created the physical directory, file, and virtual directory, you grant schema privileges to read or to read and write the contents of the virtual directory. If you haven’t created the physical file or directory, that won’t impede creating the virtual directory and granting permissions because they act independently.

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. It is actually a Best Practice to separate the source and log files.

1. Create the IMPORTER user account by signing on as the SYSTEM administrative user. The IMPORTER user is referred to thoughout the post but you may use a different user if you would like to do so.

CREATE USER importer IDENTIFIED BY password;
GRANT CONNECT, resource, CREATE any VIEW TO importer;

2. Create the virtual directory as a privileged user, SYS, and don’t forget to create the physical directory and file on the file system (the file management steps aren’t shown).

CREATE DIRECTORY download AS 'C:\Download';

3. As the privileged user, SYS, grant the read only privilege to the schema that will read the data:

GRANT READ ON DIRECTORY download TO importer;

Alternatively, if you want to write log or exception files to the same directory, you grant read and write like the following. This example will require you to grand both read and write permissions.

GRANT READ, WRITE ON DIRECTORY download TO importer;

4. Create a Comma Separated Value (CSV) file, like this, in the target directory:

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

Task #2

1. Create an External Table that depends on a Comma Separated Value (CSV) file, but doesn’t write errors during the read from the file. This example depends on your using the IMPORTER user, created in a prior preparation steps. The following character table uses oracle_loader method to read an external file.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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 that you’ve granted write privileges because it log activity and errors to external files.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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 in the BADFILE, DISCARDFILE, or LOGFILE, 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

Alternative Task #2

You can also use position specific files. Position specific files are the only way to import dates that aren’t in conforming patterns. The following is a quick example of leveraging non-conforming date formats:

, release_date  CHAR(10) date_format DATE mask "yyyy/mm/dd"

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

The following uses field length strengths by data type:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

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 position numbers:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

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;