Overview on Oracle SQL*Loader

This article will give you a general overview of the syntax used in SQL*Loader. The article will not go into any detailed level of the syntax and the associated arguments/options to the SQL*Loader utility.

The command line syntax is comprised of the command itself (sqlldr), two mandatory arguments and several optional arguments. The arguments are specified as keywords assigned a value, e.g. LOG=<filename>. Below is a short description of some of the arguments, for an extensive and full description of all the keywords please read ch. 4 in the Oracle 9i Utilities Guide and ch. 6 in the Oracle 8i Utilities Guide.

Mandatory arguments:

username/password

CONTROL=<filename>

Optional arguments:

LOG=<filename> Specifies a log file

BAD=<filename> Specifies the file where all bad records are put

DATA=<filename> Specifies the data to be loaded into the database

DISCARD=<filename> Specifies where the discarded records go

ERRORS=<integer> Specifies number of errors allowed before termination

DIRECT=<TRUE|FALSE> Specifies if loading method is DIRECT or CONVENTIONAL

PARFILE=<filename> Specifies the parameter file to be used by SQL*Loader

If the username/password argument is not specified, you will be prompted for it if there is a 2nd argument on the command line. If there are no arguments at all on the command line, e.g. sqlldr , you will get the help page for SQL*Loader. Here you can see what options are available to you.

All the arguments can be supplied on the command line, but they can also be put into a parameter file, which will be read by SQL*Loader if the PARFILE argument is used. If SQL*Loader is not supplied with a username/password or a control file, you will be prompted for the missing information. Any other argument can be left out and will take a default value. The are more arguments available and they can be listed by running the sqlldr command without any arguments. For a more detailed description of all arguments, please refer to the Utilities Guide.

The control file contains further parameters used by SQL*Loader to load the data. These are described in the Utilities Guide.

Example:

——–

sqlldr scott/tiger control=emp.ctl log=emp.txt direct=true

This example will load data into the database using the DIRECT method. Furthermore, the data will be loaded into the schema of SCOTT. The control file emp.ctl will hold all the other directives (arguments) needed by SQL*Loader to

load the data into the proper table(s).