6
SQL*Loader Command-Line Reference

This chapter shows you how to run SQL*Loader with command-line keywords. If you need detailed information about the command-line keywords listed here, see Chapter 5, "SQL*Loader Control File Reference".

This chapter covers the following subjects:

SQL*Loader Command Line

You can invoke SQL*Loader from the command line using certain keywords.

Additional Information: The command to invoke SQL*Loader is operating system-dependent. The following examples use the UNIX-based name, "sqlldr". See your Oracle operating system-specific documentation for the correct command for your system. If you invoke SQL*Loader with no keywords, SQL*Loader displays a help screen with the available keywords and default values. The following example shows default values that are the same on all operating systems.

sqlldr
...
Valid Keywords:


       userid -- Oracle username/password
      control -- Control file name 
          log -- Log file name 
          bad -- Bad file name
         data -- Data file name
      discard -- Discard file name
   discardmax -- Number of discards to allow
                (Default all)
         skip -- Number of logical records to skip
                (Default 0)
         load -- Number of logical records to load
                (Default all)
       errors -- Number of errors to allow
                (Default 50)
         rows -- Number of rows in conventional path bind array
                or between direct path data saves
                (Default: Conventional Path 64, Direct path all)
     bindsize -- Size of conventional path bind array in bytes
                (System-dependent default)
       silent -- Suppress messages during run 
                (header, feedback, errors, discards, partitions, all)
       direct -- Use direct path
                (Default FALSE)
      parfile -- Parameter file: name of file that contains 
                parameter specifications
     parallel -- Perform parallel load
                (Default FALSE)
     readsize -- Size (in bytes) of the read buffer
         file -- File to allocate extents from

Using Command-Line Keywords

Keywords are optionally separated by commas. They are entered in any order. Keywords are followed by valid arguments.

For example:

SQLLDR CONTROL=foo.ctl, LOG=bar.log, BAD=baz.bad, DATA=etc.dat 
   USERID=scott/tiger, ERRORS=999, LOAD=2000, DISCARD=toss.dis,
   DISCARDMAX=5

Specifying Keywords in the Control File

If the command line's length exceeds the size of the maximum command line on your system, you can put some of the command-line keywords in the control file, using the control file keyword OPTIONS. See OPTIONS .

They can also be specified in a separate file specified by the keyword PARFILE (see PARFILE (parameter file)). These alternative methods are useful for keyword entries that seldom change. Keywords specified in this manner can still be overridden from the command line.

Command-Line Keywords

This section describes each available SQL*Loader command-line keyword.

BAD (bad file)

BAD specifies the name of the bad file created by SQL*Loader to store records that cause errors during insert or that are improperly formatted. If a filename is not specified, the name of the control file is used by default with the .BAD extension. This file has the same format as the input datafile, so it can be loaded by the same control file after updates or corrections are made.

A bad file filename specified on the command line becomes the bad file associated with the first INFILE statement in the control file. If the bad file filename was also specified in the control file, the command-line value overrides it.

BINDSIZE (maximum size)

BINDSIZE specifies the maximum size (bytes) of the bind array. The size of the bind array given by BINDSIZE overrides the default size (which is system dependent) and any size determined by ROWS. The bind array is discussed on Determining the Size of the Bind Array . The default value is 65536 bytes. See also READSIZE (read buffer).

CONTROL (control file)

CONTROL specifies the name of the control file that describes how to load data. If a file extension or file type is not specified, it defaults to CTL. If omitted, SQL*Loader prompts you for the file name.

Note: If your control filename contains special characters, your operating system will require that they be escaped. See your operating system documentation.

Note also that if your operating system uses backslashes in its filesystem paths, you need to keep the following in mind:

a backslash followed by a non-backslash will be treated normally.
Two consecutive backslashes are treated as one backslash.
Three consecutive backslashes will be treated as two backslashes.
Placing the path in quotes will eliminate the need to escape multiple backslashes. However, note that some operating systems require that quotes themselves be escaped.

DATA (data file)

DATA specifies the name of the data file containing the data to be loaded. If a filename is not specified, the name of the control file is used by default. If you do not specify a file extension or file type the default is .DAT.

Note: if you specify a file processing option when loading data from the control file a warning message will be issued.

DIRECT (data path)

DIRECT specifies the data path, that is, the load method to use, either conventional path or direct path. TRUE specifies a direct path load. FALSE specifies a conventional path load. The default is FALSE. Load methods are explained in Chapter 8, "SQL*Loader: Conventional and Direct Path Loads".

DISCARD (discard file)

DISCARD specifies a discard file (optional) to be created by SQL*Loader to store records that are neither inserted into a table nor rejected. If a filename is not specified, it defaults to DSC.

This file has the same format as the input datafile. So it can be loaded by the same control file after appropriate updates or corrections are made.

A discard file filename specified on the command line becomes the discard file associated with the first INFILE statement in the control file. If the discard file filename is specified also in the control file, the command-line value overrides it.

DISCARDMAX (discards to disallow)

DISCARDMAX specifies the number of discard records that will terminate the load. The default value is all discards are allowed. To stop on the first discarded record, specify one (1).

ERRORS (errors to allow)

ERRORS specifies the maximum number of insert errors to allow. If the number of errors exceeds the value of ERRORS parameter, SQL*Loader terminates the load. The default is 50. To permit no errors at all, set ERRORS=0. To specify that all errors be allowed, use a very high number.

On a single table load, SQL*Loader terminates the load when errors exceed this error limit. Any data inserted up that point, however, is committed.

SQL*Loader maintains the consistency of records across all tables. Therefore, multi-table loads do not terminate immediately if errors exceed the error limit. When SQL*loader encounters the maximum number of errors for a multi-table load, it continues to load rows to ensure that valid rows previously loaded into tables are loaded into all tables and/or rejected rows filtered out of all tables.

In all cases, SQL*Loader writes erroneous records to the bad file.

FILE (file to load into)

FILE specifies the database file to allocate extents from. It is used only for parallel loads. By varying the value of the FILE parameter for different SQL*Loader processes, data can be loaded onto a system with minimal disk contention. For more information, see Parallel Data Loading Models .

LOAD (records to load)

LOAD specifies the maximum number of logical records to load (after skipping the specified number of records). By default all records are loaded. No error occurs if fewer than the maximum number of records are found.

LOG (log file)

LOG specifies the log file which SQL*Loader will create to store logging information about the loading process. If a filename is not specified, the name of the control file is used by default with the default extension (LOG).

PARFILE (parameter file)

PARFILE specifies the name of a file that contains commonly-used command-line parameters. For example, the command line could read:

SQLLDR PARFILE=example.par

and the parameter file could have the following contents:

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

Note: Although it is not usually important, on some systems it may be necessary to have no spaces around the equal sign ("=") in the parameter specifications.

PARALLEL (parallel load)

PARALLEL specifies whether direct loads can operate in multiple concurrent sessions to load data into the same table. For more information on PARALLEL loads, see Parallel Data Loading Models .

READSIZE (read buffer)

The command-line parameter READSIZE lets you specify (in bytes) the size of the read buffer. The default value is 65536 bytes, however, you can specify a read buffer of any size depending on your system.

Since, when using the conventional path method, the bind array is limited by the size of the read buffer, the advantage of a larger read buffer is that more data can be read before a commit is required.

For example:

sqlldr scott/tiger control=ulcas1.ctl readsize=1000000

enables SQL*Loader to perform reads from the external datafile in chunks of 1000000 bytes before a commit is required.

Note: The default value for both the READSIZE and BINDSIZE parameters is 65536 bytes. If you have specified a BINDSIZE that is smaller than the size you specified for READSIZE, the BINDSIZE value will be automatically increased the specified value of READSIZE.

Also, if the READSIZE value specified is smaller than the BINDSIZE value, the READSIZE value will be increased.

Note also that this parameter is not related in any way to the READBUFFERS keyword used with direct path loads.

ROWS (rows per commit)

Conventional path loads only: ROWS specifies the number of rows in the bind array. The default is 64. (The bind array is discussed on Determining the Size of the Bind Array .)

Direct path, loads only: ROWS identifies the number of rows you want to read from the data file before a data save. The default is to save data once at the end of the load. For more information, see Data Saves .

Because the direct load is optimized for performance, it uses buffers that are the same size and format as the system's I/O blocks. Only full buffers are written to the database, so the value of ROWS is approximate.

SILENT (feedback mode)

When SQL*Loader begins, a header message like the following appears on the screen and is placed in the log file:

SQL*Loader:   Production on Wed Feb 24 15:07:23... 
Copyright (c) Oracle Corporation...

As SQL*Loader executes, you also see feedback messages on the screen, for example:

Commit point reached - logical record count 20

SQL*Loader may also display data error messages like the following:

Record 4: Rejected - Error on table EMP
ORA-00001: unique constraint <name> violated

You can suppress these messages by specifying SILENT with an argument.

For example, you can suppress the header and feedback messages that normally appear on the screen with the following command-line argument:

SILENT=(HEADER, FEEDBACK)

Use the appropriate keyword(s) to suppress one or more of the following:

HEADER	Suppresses the SQL*Loader header messages that normally appear on the screen. Header messages still appear in the log file.
FEEDBACK	Suppresses the "commit point reached" feedback messages that normally appear on the screen.
ERRORS	Suppresses the data error messages in the log file that occur when a record generates an Oracle error that causes it to be written to the bad file. A count of rejected records still appears.
DISCARDS	Suppresses the messages in the log file for each record written to the discard file.
PARTITIONS	This Oracle8i option for a direct load of a partitioned table disables writing the per-partition statistics to the log file
ALL	Implements all of the keywords.

SKIP (records to skip)

SKIP specifies the number of logical records from the beginning of the file that should not be loaded. By default, no records are skipped.

This parameter continues loads that have been interrupted for some reason. It is used for all conventional loads, for single-table direct loads, and for multiple-table direct loads when the same number of records were loaded into each table. It is not used for multiple table direct loads when a different number of records were loaded into each table. See Continuing Multiple Table Conventional Loads for more information.

USERID (username/password)

USERID is used to provide your Oracle username/password. If omitted, you are prompted for it. If only a slash is used, USERID defaults to your operating system logon. A Net8 database link can be used for a conventional path load into a remote database. For more information about Net8, see the Net8 Administrator's Guide For more information about database links, see Oracle8i Distributed Database Systems.

Index Maintenance Options

Two new, Oracle8i index maintenance options are available (default FALSE):

SKIP_UNUSABLE_INDEXES={TRUE | FALSE}
SKIP_INDEX_MAINTENANCE={TRUE | FALSE}

SKIP_UNUSABLE_INDEXES

The SKIP_UNUSABLE_INDEXES option applies to both conventional and direct path loads.

The SKIP_UNUSABLE_INDEXES=TRUE option allows SQL*Loader to load a table with indexes that are in Index Unusable (IU) state prior to the beginning of the load. Indexes that are not in IU state at load time will be maintained by SQL*Loader. Indexes that are in IU state at load time will not be maintained but will remain in IU state at load completion.

However, indexes that are UNIQUE and marked IU are not allowed to skip index maintenance. This rule is enforced by DML operations, and enforced by the direct path load to be consistent with DML.

Load behavior with SKIP_UNUSABLE_INDEXES=FALSE differs slightly between conventional path loads and direct path loads:

On a conventional path load, records that are to be inserted will instead be rejected if their insertions would require updating an index.
On a direct path load, the load terminates upon encountering a record that would require index maintenance be done on an index that is in unusable state.

SKIP_INDEX_MAINTENANCE

SKIP_INDEX_MAINTENANCE={TRUE | FALSE} stops index maintenance for direct path loads but does not apply to conventional path loads. It causes the index partitions that would have had index keys added to them instead to be marked Index Unusable because the index segment is inconsistent with respect to the data it indexes. Index segments that are not affected by the load retain the Index Unusable state they had prior to the load.

The SKIP_INDEX_MAINTENANCE option:

applies to both local and global indexes.
can be used (with the PARALLEL option) to do parallel loads on an object that has indexes.
can be used (with the PARTITION keyword on the INTO TABLE clause) to do a single partition load to a table that has global indexes.
puts a list (in the SQL*Loader log file) of the indexes and index partitions that the load set into Index Unusable state.

Exit Codes for Inspection and Display

Oracle SQL*Loader provides the results of a SQL*Loader run immediately upon completion. Depending on the platform, as well as recording the results in the log file, the SQL*Loader may report the outcome also in a process exit code. This Oracle SQL*Loader functionality allows for checking the outcome of a SQL*Loader invocation from the command line or script. The following load results return the indicated exit codes:

Result	Exit Code
All rows loaded successfully	EX_SUCC
All/some rows rejected	EX_WARN
All/some rows discarded	EX_WARN
Discontinued load	EX_WARN
Command line/syntax errors	EX_FAIL
Oracle errors fatal to SQL*Loader	EX_FAIL
OS related errors (like file open/close, malloc, etc.)	EX_FTL

For UNIX the exit codes are as follows:

EX_SUCC	0 
EX_FAIL	1 
EX_WARN	2 
EX_FTL	3

You can check the exit code from the shell to determine the outcome of a load. For example, you could place the SQL*Loader command in a script and check the exit code within the script:

#!/bin/sh 
sqlldr scott/tiger control=ulcase1.ctl log=ulcase1.log 
retcode=`echo $?` 
case "$retcode" in 
0) echo "SQL*Loader execution successful" ;; 
1) echo "SQL*Loader execution exited with EX_FAIL, see logfile" ;; 
2) echo "SQL*Loader exectuion exited with EX_WARN, see logfile" ;; 
3) echo "SQL*Loader execution encountered a fatal error" ;; 
*) echo "unknown return code";; 
esac

6SQL*Loader Command-Line Reference