SQLLoader - An overview with examples

Sqlloader is an Oracle supplied utility that can be used to load data into table(s) within an Oracle database from flat files. It is highly efficient, reliable and commonly used.

These pages will demonstrate how it can be used and provide suitable scripts that can easily be adapted.

Several files need to be prepared before successfully using sqlloader. These include a calling command file (optional), a control file, a parfile (optional) and a data file. The table must pre-exist but it may or may not contain existing data.

A simple command line syntax could look like the following

sqlldr control=chf.ctl userid=username/password

The control file would contain the following lines which indicate the name and location of the datafile, whether the table should be empty or not, the target table and the format of the input data.

load data
infile 'nov01.csv' (single quite used here is optional)
into table chf
fields terminated by ',' optionally enclosed by '"'
(
PHONE,
MARKETING_SEGMENT_CODE,
PRE_POST_FLAG,
PL_SEGMENT,
BILLING_ENGINE,
CONNECT_DATE DATE(8) "YYYYMMDD" ,
FIRST_CUST,
SELECT_CUST,
SEGMENT_CODE,
ADVERT_ID1,
ADVERT_ID2,
ADVERT_ID3,
ADVERT_ID4,
ADVERT_ID5,
ADVERT_ID6,
ADVERT_ID7,
ADVERT_ID8
)

I have never found a way of hiding the userid/password from the command line when callling a sqllloader ctl file until fairly recently.

It is possible to use a parfile in sqllloader similar to that used in export/import

The format would be as follows

sqlldr parfile=example.par
The parameter file could have the following contents:
userid=scott/tiger
control=example.ctl
errors=9999
log=example.log

I am sure everybody else but me knew that already but what the hell !

Types of Load

In the example above the default value of INSERT is taken. Options are APPEND, REPLACE, INSERT or TRUNCATE.

The format would be:

load data
infile 'nov01.csv'
APPEND | INSERT | TRUNCATE

APPEND adds the data to the table if whether the table is empty or not

INSERT is the default value. The table must be empty otherwise an error message is returned.

REPLACE - uses an implicit truncate of the table to replace existing data. It does not look at specific rows but rather removes all rows and inserts new ones, even if the new data is the same as the original data.

TRUNCATE - requires all RI constraints on the table to be disabled.

This is the most efficient.

Note that the TRUNCATE command is not the same as the SQL TRUNCATE command as is more inefficient.

Files used ( required and optional)

CONTROL FILE - informs sqlloader what data is to be loaded where and any actions that are necessary.

PARFILE can be used to store the username and password so that it can be handy from a ps -ef| o/s command. Similar to the use of parfile with the export/import utility.

sqlldr parfile=example.par

The example.par could have the following contents:

userid=scott/tiger
control=example.ctl
errors=9999
log=example.log

LOG file - output file show details of records processed, timings and results. Produced automatically if not specified in the parfile, the default name will be the same as the data file but with a extension of log.

BAD file - output file produced to hold records that do not contain valid data. An example would be a delimited file that does not contain a record for a not null column. Produced automatically if not specified in the parfile, the default name will be the same as the data file but with an extension of bad. If no discards are found the file is not created.

DISCARD file - an output file that contains records that do not meet the criteria specified with in the control file. Produced when specified in the parfile, the default name will be the same as the data file but with an extension of dsc. If no discards are found the file is not created.

Direct or Normal load

To enhance performance a direct load can be used. This is specified in the control file.