Using the Oracle Bulk Loader

Index

Creating a Control File
Using a Separate Data File
Representing Dates
Loading Your Data

Creating a Control File

There are two approaches to control files.

A. Data is Loaded from the Control File

Perhaps the simplest form of control file has the following form, where the data to be loaded is found at the end of the control file. All capital letters are optional.

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

The purpose of this file is to load data into the relation test from the handout Getting Started With Oracle. Recall that this relation has two attributes, i and s, of types integer and char(10), respectively. The meaning of the lines, in order, are:

LOAD DATA is required at the beginning of a control file (which this file is).
INFILE * says that the data will appear in this file, rather than in a separate data file, which would replace the *.
The insertion will be into the relation named test. The table to receive the data must already exist in the database. Futhermore, the table must be empty. Otherwise, use "APPEND INTO TABLE test" instead.
In the data, the comma will be used to separate the values of different attributes.
On each line of the data, the value for attribute i will appear, followed by the value of attribute s. As it said on the 4th line, these values will be separated by a comma.
BEGIN DATA says that what follows are lines representing tuples to be inserted.
The first tuple has i=1 and a='foo'
Names of column to load are enclosed in parentheses.

Warning: A very common error is to assume that in a load file blanks following a comma are ignored (as they are in most programming languages). This assumption is wrong, and it leads to a number of very frustrating problems that you won't notice until you try to query your loaded data.

Notice that the third tuple in the load file above has a blank in front of baz. Thus, the value of attribute s for the third tuple is

     ' baz'

that is, the four-character string with a blank in front. Later, we might search for tuples that have second component 'baz' and expect to find it, but we won't, because 'baz' and ' baz' are different strings.

B. Data is Loaded from a Data File

We may put the data to be loaded into a file different from the control file. Here is a simple example.

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

The meaning of these lines is as follows:

LOAD DATA is required at the beginning of a control file.
INFILE test.txt says that the data will appear in the file test.txt.
The data in test.txt will be appended to the relation test. If we do not say APPEND, then the relation test must be empty initially.
In the data file, the comma will be used to separate the values of different attributes.
Names of columns to load are enclosed in parentheses.

The content of the data file test.txt is:

1,foo
2,bar
3, baz
4,glorp
5,snarf

If test were initially empty, then the final contents of test will be exactly the same as in the previous example.

Loading Dates

The DATE datatype can have its data represented in a format you specify with considerable flexibility. First, you declare the table to be loaded to have a DATE type for the attribute(s) that represent dates. Here is a simple example:

CREATE TABLE Foo (
     i INTEGER,
     d DATE
);

Then, in the control file, when you describe the attributes of the table being loaded, you follow them by their types; any attribute can be followed by its type, but in the example above, we shall specify a type for only the date-valued attribute d.

Following the DATE type is a date mask that describes the format your date data will use. The date mask is a double-quoted string with the following conventions:

Sequences of letters beginning with 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 also acceptable, e.g., Mon is a month field.

Other characters, such as the dash, are treated literally, and must appear in your data if you put them in the mask.

The fields you use are maximum lengths for the corresponding values in your data. However, the data can be shorter.
Here is an example load file.
```
LOAD DATA
INFILE *
INTO TABLE Foo
FIELDS TERMINATED BY ','
(i,d DATE "dd-mm-yyyy")
BEGINDATA
1,01-02-1234
2,3-4-1997
```
Notice that, as in the second data tuple above, a field can be shorter than the corresponding field of the mask. The punctuation - tells the loader that the day and month fields of the second tuple terminate early.
Using the Loader
sqlldr is a UNIX-level command, like sqlplus. The form of a load command is:
```
     sqlldr userid=<yourName>/<yourPasswd> control=<ctlFile> log=<logFile>
```
The arguments on the command line are a little unusual for UNIX commands. You have to follow the word userid (capitals OK) by an equal sign and your login name, a slash, your password, the at-sign, and cs. The safer form of login, where you give your name and let the system prompt you for a password, applies to sqlldr as well.
Then comes a control file, the file in which the loader commands such as the ones above, are found. Finally, you have to give the name of another file, the log file, in which some useful information will be placed by sqlldr to tell you what it has done, including a discripition of any errors that occured during the load. For instance, if Sally, with password etaoinshrdlu wishes to run the control file test.ctl and have the log output stored in her file test.log, then (assuming she has the environment given by file ~oracle/ora.env, as discussed in Getting Started With Oracle), Sally can execute:
```
     sqlldr userid=sally/etaoinshrdlu control=test.ctl log=test.log
```