Database Reference
In-Depth Information
SQL*Loader Control File
The control file is a text file that SQL*Loader uses to locate the data file or files and to deter-
mine how data will be formatted, how to handle rejected records, the destination table, field
names, whether or not to append rows, and how the data will be manipulated.
There are three main ordered sections to the control file:
■
Session-wide information
■
Table and field-list information
■
An optional input data section
The syntax of the control file is case-insensitive, but quoted text is considered a literal.
Statements can extend over multiple lines. Comments begin with two hyphens and go to
the end of the line. Comments are not supported in the optional input data section.
In this simplified control file example, a SQL*Loader express mode session generated a
copy of a control file and wrote it to the log file; we have copied the control file section and
modified it by adding the
BADFILE
and
DISCARDFILE
parameters.
--Generated control file for possible reuse:
OPTIONS(EXTERNAL_TABLE=EXECUTE, TRIM=LRTRIM)
LOAD DATA
INFILE 'countries.dat'
BADFILE 'countries.bad'
DISCARDFILE 'countries.dsc'
APPEND
INTO TABLE COUNTRIES
WHEN COUNTRY_ID = 'AR'
FIELDS TERMINATED BY ","
(
COUNTRY_ID,
COUNTRY_NAME,
REGION_ID
)
--End of generated control file for possible reuse.
This section describes some of the key items in the control file.
LOAD DATA
Indicates the beginning of a new data load.
INFILE
Is the name of a data file to load.
BADFILE
Names the file to write the rejected records to.
DISCARDFILE
Is the target for records not selected in the
WHERE
clause.
APPEND
Is used to add rows to a non-empty table; use
INSERT
to add rows to an empty table.
INTO TABLE
Is used to identify tables, fields, and their associated datatypes. Defines the
translation between records to be loaded and the database tables.
Search WWH ::
Custom Search