Using SQL*Loader to Load Data

Two additional data-movement utilities, Expert and Import, are covered. SQL*Loader loads data from external files into tables in the Oracle database. SQL*Loader requires two primary files: the data file, which contains the information to be loaded and the control file, which contains information on the format of the data, the records and fields within the file, the order in which they are to be loaded and even when needed, the names of the multiple files that will be used for data. You can also combine the control file information into the data file itself, although the two are usually separated to make it easier to reuse the control file.

When executed, SQL*Loader will aromatically create a log file and a "bad" file. The log file records the status of the load, such as the number of rows processed and the number of rows committed. The "bad" file will contain all the rows that were rejected during the loader due to data errors, such as no unique values in primary key columns.

Within the control file, you can specify additional commands to govern the load criteria. If these criteria are not met by a row, the row will be written to a "discard" file. The log, bad and discard files will have the extensions .log, .bad and .dsc respectively. Control files are typically given the extension .ctl.

The Control File
The control file tells Oracle how to read and load the data. The control file tells SQL*Loader where to find the source data for the load and the tables into which to load the data, along with any other rules that must be applied during the load processing. The control file is created at the operating system level, using any text editor that enables you to save plain text files. The following listing shows a sample control file for loading data into the lodging table:

C:> EDIT control_file.ctl
LOAD DATA
INFILE 'lodging.dat'
INTO TABLE lodging
(Lodging POSITION(01:15) CHAR,
Longname POSITION(01:15) CHAR,
Manager POSITION(01:15) CHAR,
Address POSITION(01:15) CHAR)

ð In this example, data is loaded from the file lodging.dat into the lodging table. The lodging.dat file contains the data for all four of the lodging columns with white space padding out the unused characters in those fields. Thus, the Longname column value always begins at space 16 in the file, even if the lodging value is less than 15 characters.

Loading Variable-Length Data
In the following example, a comma separates the input values:

C:> EDIT control_file.ctl
LOAD DATA
INFILE 'lodging.dat'
BADFILE 'c:\user\load\lodging.bad'
TRUNCATE
INTO TABLE lodging
FIELDS TERMINATED BY ","
(Lodging, Longname, Manager, Address)

ð The field terminated by "," clause tells SQL*Loader that during the load, each column value will be terminated by a comma. In this example, the name of the bad file is specified by the badfile clause. In general, the name of the bad file is only given when you wish to redirect the file to a different directory. Here, also shows the use of the truncate clause within a control file. When this control file is executed by SQL*Loader, the lodging table will be truncated before the start of the load. In addition to truncate, you can use the following options:

append	Use to add rows to the table.
insert	Use to add rows to an empty table. If the table is not empty, the load will abort with an error.
replace	Use to empty the table and then add the new rows. The user must have DELETE privilege on the table.

Starting the Load
SQL*Loader is started via the SQLLDR command at the operating system prompt. When you execute SQLLDR, you need to specify the control file, username/password and other critical load information as shown below:

If the userid keyword is omitted, you will be asked for it. If a slash is given after the equal sign, an OPS$ login default ID and password will be used. You also can use a Net8 database specification string to log on to a remote database and load the data into it. For example, your command may start:

C:\> SQLLDR userid=usernm/mypass@dev

The data to be loaded is in a file called lodging.dat and consists of two records:

C:|> EDIT lodging.dat
GoodLodging, Good Lodging Record, John Goodman, 123 Okeedokee Drive
GoodLodging 1, Lodging Record Ok, Mark Jon, 155 Okeedokee Drive

ð The data is separated by commas and we don't wish to delete the data previously loaded into lodging table, so the control file will look like this:

C:> EDIT lodging.ctl
LOAD DATA
INFILE 'lodging.dat'
APPEND
INTO TABLE lodging
FIELDS TERMINATED BY ","
(Lodging, Longname, Manager, Address)

Next, run SQLLDR and tell it to use the control file:

C:> SQLLDR practice/practice control=lodging.ctl log=lodging.log

Control File Syntax Notes
The syntax for the into table clause includes a when clause. The when clause, show in the preceding listing, services as a filter applied to rows prior to their insertion into the table. For example, you can specify

WHEN Manager='TOM CRANMER'

When you load DATA date datatype values, you can specify a date mask.

HireDate POSITION (1:11) DATE "Mon-DD-YYYY"