Using the Oracle Bulk Loader 

• Overview
• Creating the Control File
• Creating the Data File
• Loading Your Data
• Loading Without a Separate Data File
• Loading DATE Data
• Entering NULL Values

LOAD DATA
INFILE <dataFile>
APPEND INTO TABLE <tableName>
FIELDS TERMINATED BY '<separator>'
(<list of all attribute names to load>)

• <dataFile> is the name of the data file. If you did not give a file name extension for <dataFile>, Oracle will assume the default extension ".dat". Therefore, it is a good idea to name every data file with an extension, and specify the complete file name with the extension.
• <tableName> is the name of the table to which data will be loaded. Of course, it should have been created already before the bulk load operation.
• The optional keyword APPEND says that data will be appended to <tableName>. If APPEND is omitted, the table must be empty before the bulk load operation or else an error will occur.
• <separator> specifies the field separator for your data file. This can be any single character. It is a good idea to use a character that you know will never appear in the data, so the separator will not be confused with data fields.
• Finally, list the names of attributes of <tableName> that are set by your data file, separated by commas and enclosed in parentheses. This list need not be the complete list of attributes in the actual schema of the table, nor must it be arranged in the same order as the attributes when the table was created -- sqlldr will match attributes to by their names in the table schema. Any attributes unspecified in the list of attributes will be set to NULL.

LOAD DATA
INFILE test.dat
INTO TABLE test
FIELDS TERMINATED BY '|'
(i, s)

1|foo
2|bar
3| baz

(1, 'foo')
(2, 'bar')
(3, ' baz')

Note: Oracle literally considers every single line to be one tuple, even an empty line! When it tries to load data from an empty line, however, an error would occur and the tuple will be rejected. Some text editors love to add multiple newlines to the end of a file; if you see any strange errors in your .log file about tuples with all NULL columns, this may be the cause. It shouldn't affect other tuples loaded.

sqlldr <yourName>@weasel control=<ctlFile> log=<logFile> bad=<badFile>

As a concrete example, if sally wishes to run the control file test.ctl and have the log output stored in test.log, then she should type

sqlldr sally control=test.ctl log=test.log

LOAD DATA
INFILE *
INTO TABLE test
FIELDS TERMINATED BY '|'
(i, s)
BEGINDATA
1|foo
2|bar
3| baz

CREATE TABLE foo (
    i int,
    d date
);

• Sequences of d, m, or y, denote fields in your data that will be interpreted as days, months, years, respectively. As with almost all of SQL, capitals are equally acceptable, e.g., MM is a month field.
• The lengths of these fields specify the maximum lengths for the corresponding values in your data. However, the data can be shorter.
• Other characters, such as dash, are treated literally, and must appear in your data if you put them in the mask.

LOAD DATA
INFILE *
INTO TABLE foo
FIELDS TERMINATED BY '|'
(i, d DATE 'dd-mm-yyyy')
BEGINDATA
1|01-01-1990
2|4-1-1998

You may specify NULL values simply by entering fields in your data file without content. For example, if we were entering integer values into a table with schema (a, b, c) specified in the .ctl file, the following lines in the data file:

3||5
|2|4
1||6
||7

would result in inserting the following tuples in the relation:

(3, NULL, 5)
(NULL, 2, 4)
(1, NULL, 6)
(NULL, NULL, 7)

Keep in mind that any primary keys or other constraints requiring that values be non-NULL will reject tuples for which those attributes are unspecified.

Note:If the final field in a given row of your data file will be unspecified (NULL), you have to include the line TRAILING NULLCOLS after the FIELDS TERMINATED BY line in your control file, otherwise sqlldr will reject that tuple. sqlldr will also reject a tuple whose columns are all set to NULL in the data file.

If you do not wish to enter values for any row of a given column, you can, as mentioned above, leave that column out of the attribute list altogether.