Database Reference
In-Depth Information
FIELDS TERMINATED BY
Defines the field termination or field separation character.
WHEN
Specifies one or more field conditions to determine which records to load. Records
that do not meet the criteria are discarded.
Finally, we define the fields to be loaded.
SQL*Loader Input Data and Data Files
The INFILE specified in the SQL*Loader control file contains the records to be loaded
into the database table or tables. A data file may be fixed record format, variable record
format, or stream record format. Specify the record format in the control file by using
the
INFILE
parameter.
Fixed Record Format
In a fixed record format data file, all records in the file are the same byte length. Fixed
format is simple to specify, and load performance is better than for variable or stream
record loads. Indicate fixed record format and record length
n
as in this example:
INFILE 'datafile_name' "fix n"
Variable Record Format
When using a variable record format data file, the length of each record is included in a
character field at the beginning of each record. Variable is more flexible than fixed record
format and performs better than stream record format. Indicate variable record format and
the length of the character field at the beginning of each record
n
as in this example:
INFILE 'datafile_name' "var n"
Stream Record Format
When you don't have the specific size of each record, use the stream record format.
SQL*Loader uses the record terminator to determine the end of the line. This is the most
flexible format; however, performance may suffer. Indicate stream record format as in this
example, where
terminator_string
is the character string that is used to end a record:
INFILE 'datafile_name' ["str terminator_string"]
If no
terminator_string
value is specified, then the default record terminator is the
line feed character,
\n
on Unix, and
\n
or
\r\n
(whichever comes first in the record) on
Windows. Here's a data file example that would use the stream record format:
C:\Temp>type countries.dat
UR,Uruguay,2
TK,Turkey,4
SY,Syria,4
Search WWH ::
Custom Search