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

The SQL*Loader control file must not be confused with the control file

It specifies the behaviour of SQL*Loader.

A control file contains three sections.

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

The second section contains one or more INTO TABLE blocks.

The (optional) third section contains input data.

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

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.

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.

infile 'precious_data.txt' "var 4"

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.

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

See example 3.

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

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

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 is used to selectively load data (according to a criteria stated after the when) into the table to be loaded. See also example 3

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

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.

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

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.