René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

SQL*Loader

SQL*Loader can be used to load data from a file into Oracle tables. It is possible to selectivly exclude records and transform records while they're loaded. The load produces a log file, bad files and discard files. The control file specifies the behaviour of the
SQL*Loader is started with sqlldr

Control file

The SQL*Loader control file must not be confused with the control file
It specifies the behaviour of SQL*Loader.

Three sections

A control file contains three sections.

1st section

The first section contains global options: bindsize, rows, records to skip etc. Also, the INFILE clause determines the location of the input data.

2st section

The second section contains one or more INTO TABLE blocks.

3st section

The (optional) third section contains input data.

Control file statements

LOAD DATA

This statement indicates that this is the beginning of a new data load.

INFILE

The INFILE statement specifies the file to be loaded. It also indicates whether the records are fixed length, variable length or stream format;
The following infile statement indicates that each record to be loaded has 11 bytes. Note, that carriage returns and line feeds also count as bytes.

Fixed format

infile 'precious_data.txt' "fix 11"
The following infile statement indicates that each record to be loaded is of variable length. A record starts either at the beginning of the input file or after a carriage return/line feed. The length of each record is indicated at the beginning of each record and consists of 4 bytes.

Variable format

infile 'precious_data.txt' "var 4"

Stream format

The following infile statement indicates that the input data is in stream format. That is, SQL*Loader searches for a record seperator when it reads the input data. The record seperator in this case is either a bar (|) or a line feed.
infile 'precious_data.txt' "str '|\n'"
In stream format, it would also be possible to say \t for tabulator, \f for form feed, \v for vertical tabulator, \r for carriage return.

INFILE *

If the data to be loaded is located in the control file itself, the file name is a star.

DISCARDFILE

See example 3.

INTO TABLE

The into table statement defines the relationship between the fields in an Oracle table and the records in the datafile to be loaded.

APPEND

One of the options to be used if the table is non empty. See also INSERT.

INSERT

FIELDS TERMINIATED BY

If the fields of the data that is being loaded are not fixed width, it must be specified how they're seperated.
See Example 2.

WHEN

When is used to selectively load data (according to a criteria stated after the when) into the table to be loaded. See also example 3

BEGINDATA

If the data to be loaded is in the control file itself, the start of the data is marked with a BEGINDATA statement.

Bad file

If a record cannot be loaded because it is rejected (either by Oracle itself or SQL*Loader), it will be written into the bad file.

Discard file

The discard file contains records that didn't meet any of the record selection critierias.

Some simple examples

Exmple one: this example shows how to insert records into an empty table. One column is filled with the current sysdate.
Exmple two shows how to insert records that are seperated by a comma.
Exmple three loads data selectively according to a criteria.
Exmple four is similar to example three but loads two tables.