Allows you to specify runtime parameters in the control file, rather than on the command line. Values specified on the command line override values specified in the OPTIONS statement of the control file. If specified, the OPTIONS statement must precede the LOAD keyword. See the earlier Section 16.3.1 section for information on the OPTIONS parameters.

Loaded data is not logged, which improves load performance. (Other changes to the database are logged.) This option can be specified for a direct path load only; it cannot be specified for a conventional load.

Loaded data is logged in the redo log. This option is the default for direct path loads, and all conventional loads are recoverable.

Used with the optional keyword DATA to specify the characteristics of the input data to be loaded by SQL*Loader.

An operating system-dependent, file processing options string may be specified for each input file. See your Oracle operating system specific documentation for more information.

Used for direct loads only; specifies the number of buffers (n) to be used to read logical records. The default is 4, and this value should not be changed unless an ORA-02374 (no more slots for read buffer queue) error is encountered.

This keyword, which may be used with the LOAD/CONTINUE_LOAD or INTO TABLE keywords, specifies that the table to be loaded must be empty. If the table is not empty, an error results and SQL*Loader terminates. INSERT is the default. When this keyword is specified before any INTO TABLE clause, it will control the loading of all tables, but it may be overridden by a table-loading keyword in an INTO TABLE clause.

This keyword, which may be used with the LOAD/CONTINUE_LOAD or INTO TABLE keywords, specifies that the table is to be extended by adding new rows. When this keyword is specified before any INTO TABLE clause, it will control the loading of all tables, but it may be overridden by a table-loading keyword in an INTO TABLE clause.

This keyword, which may be used with the LOAD/CONTINUE_LOAD or INTO TABLE keywords, specifies that all existing rows in the table are to be deleted, and then new rows inserted. The user running SQL*Loader must have the DELETE privilege on the table. When this keyword is specified before any INTO TABLE clause, it will control the loading of all tables, but it may be overridden by a table-loading keyword in an INTO TABLE clause.

This keyword, which may be used with the LOAD/CONTINUE_LOAD or INTO TABLE keywords, specifies that the table is to be truncated and new rows are to be inserted. The user running SQL*Loader must have the DELETE privilege on the table. When this keyword is specified before any INTO TABLE clause, it will control the loading of all tables, but it may be overridden by a table-loading keyword in an INTO TABLE clause.

Specifies the name of a file (filename) to contain records rejected by SQL*Loader. The file is only created if one or more records are rejected, and the name may be overridden by a BAD parameter on the command line.

This parameter has the same meaning as BAD, and may be used interchangeably.

Specifies the name of a file (filename) to contain records that are discarded by SQL*Loader because they do not meet any of the loading criteria. The file is only created if one or more records are discarded, and the name may be overridden by a DISCARDFILE parameter on the command line.

Has the same meaning as DISCARDFILE, and may be used interchangeably.

Limits the number of records discarded for this datafile to n. When n records have been discarded, processing of this datafile is terminated, and processing continues with the next datafile, if one exists. A different number of discards may be specified for each datafile.

The same as DISCARD, and may be used interchangeably.

Used to create one logical record from multiple physical records. SQL*Loader will add n number of physical records to form one logical record. If the number of physical records to be continued varies, CONTINUEIF must be used.

Used to create one logical record from multiple physical records. The keyword CONTINUEIF is followed by a condition that is evaluated for each physical record as it is read.

Used with CONTINUEIF, and specifies that if the condition is true in this record (as determined by the starting and ending columns optionally specified by start:end ), the next physical record is read and concatenated to the current physical record, continuing until the condition is false. If the condition is false in the current record, the current physical record is the last physical record of the current logical record. THIS is the default.

Used with CONTINUEIF, and specifies that if the condition is true in the next record (as determined by the starting and ending columns optionally specified by start:end ), the next physical record is concatenated to the current record, continuing until the condition is false. If the condition is false in the next record, the current physical record is the last physical record of the current logical record.

Used with CONTINUEIF THIS or CONTINUEIF NEXT, specifies the starting and ending column numbers in the physical record. If you omit end, the length of the continuation field is the length of the byte string or character string. If you use end, and the length of the resulting continuation field is not the same as that of the byte string or character string, the shorter one is padded. Character strings are padded with blanks, hexadecimal strings with zeros.

This test is similar to THIS, but the test is always against the last non-blank character. If the last non-blank character in this physical record meets the test, the next physical record is read and concatenated to the current physical record, continuing until the condition is false. If the condition is false in the current record, the current physical record is the last physical record of the current logical record. CONTINUEIF LAST differs from CONTINUEIF THIS and CONTINUEIF NEXT. With CONTINUEIF LAST, the continuation character is not removed from the physical record. Instead, this character is included when the logical record is assembled.

The supported operators used with the CONTINUEIF keyword are equal and not equal, which may be expressed as: = != ¬= <>

For the equal operator, the field and comparison string must match exactly for the condition to be true. For the not-equal operator, they may differ in any character.

A string of characters to be compared to the continuation field defined by start and end, according to the operator. The string must be enclosed in double or single quotation marks. The comparison is made character by character, blank padding on the right if necessary.

A string of bytes in hexadecimal format, used in the same way as the character string above. X’1FB033’ would represent the three bytes with values 1F, B0, and 33 (hex).

Retains leading whitespace when optional enclosure delimiters are not present. It also leaves trailing whitespace intact when fields are specified with a predetermined size. This keyword preserves tabs and blanks.

Used to specify the table (tablename) into which data is to be loaded. To load multiple tables, include one INTO TABLE clause for each table to be loaded. The table name may optionally be preceded by a schema. If schema is not specified, the schema of the account running SQL*Loader will be assumed. Note that the meanings of INSERT, APPEND, REPLACE, and TRUNCATE are the same as those described for LOAD and CONTINUE_LOAD.

This keyword, which applies only to direct path loads, specifies that the data to be loaded is already sorted in the sequence specified by the index indexname, which must already exist prior to the load.

This keyword, which is used only with direct path loads with APPEND, specifies that each index entry should be inserted directly into the index, one row at a time. Normally, when loading with APPEND, index entries are accumulated in a temporary storage area and merged into the existing index at the end of the load.

Used to choose rows of data to insert into a table by specifying a condition. SQL*Loader determines values for all fields in a record, then determines whether the row should be inserted by evaluating the WHEN clause. The condition may be specified using either a column name (e.g., WHEN DEPTNO='20') or using a column position number (e.g., WHEN (2) = '10'). Multiple comparisons may be combined using the AND keyword.

Specifies the default delimiter_spec clause. This default may be overridden for any particular column by specifying a different delimiter_spec after that column name.

Specifies that if the control file definition specifies more fields for a record than the record actually contains (i.e., the record is too short), any relatively positioned columns that are not present should be treated as NULL.

When used following a table_name with CONTINUE_LOAD, specifies the number of records to skip for this table before resuming the load. SKIP is used only with direct path load.

When used with direct path load, specifies that disabled check constraints or referential (foreign key) constraints should be reenabled when the load is completed. The EXCEPTIONS keyword specifies the name of an existing table (filename) that contains the ROWID of each row that has violated a constraint, along with the name of the violated constraint. The keyword DISABLED_CONSTRAINTS is optional and is used for readability only.

When used following a column_name, specifies that the number of the logical record from which this row is loaded should be used as the value for the specified column. In other words, the column is set to the logical record number.

When used following a column_name, specifies that the column be set to the value of SYSDATE, the system date.

When used following a column_name, specifies that the column should be assigned value, which will be converted automatically to match the column type. Note that the CONSTANT keyword should not be used to assign a value of NULL.

When used following a column_name, specifies that a unique value will be assigned to column_name for each row loaded. Start may be specified as n, a specific starting value; COUNT, which specifies that the sequence start with the number of rows already in the table, plus increment; or MAX, which specifies that the sequence start with the current maximum value for the column, plus incr (increment). incr may be specified as a positive number, and has a default of 1 if omitted.

Specifies a field’s position and length in the logical input record. The field position may be specified either explicitly as start or relative to the previous field as *+n, where n indicates the offset, or number of characters to be skipped, from the previous field. If *+n is used, end is derived from the datatype; otherwise, the end of the field is specified by :end.

This keyword, used only with direct path loads, is used to notify SQL*Loader that the last field of the logical record may be processed in pieces, reusing a single buffer.

This keyword, when used following a column_name, specifies that if condition is true, the column should be assigned a value of NULL.

Note that the same result can be achieved through the use of the SQL NVL function applied to the column. If you want the value of the column to be NULL for all rows inserted, simply omit the column specification altogether.

When used following a column_name, specifies that when a condition is true, the column will be assigned a value of zero if it is defined as NUMBER. The column will be assigned a value of NULL if it is defined as CHAR, DATE, or numeric EXTERNAL.