SQL*Loader Environment

SQL*Loader takes as input a control file, which controls the behavior of SQL*Loader, and one or more datafiles. Output of the SQL*Loader is an Oracle database (where the data is loaded), a log file, a bad file, and potentially a discard file.

Execution Steps:

1. Executes from the command prompt or parameter file.
2. Reads the control file.
3. Reads the data from the control file or from one or more datafiles.
4. Loads the data in the database and logs the information in the log file.
5. Places the rejected records in the bad file.
6. Places the discarded records in the discard file.

1) The parameter file:

The command line information can be saved in a parameter file. This parameter file is executed from the command prompt. A parameter file has a .par extension. Following is a sample parameter file.

Steps to create a parameter file:

• Open a text editor.
• Type in each parameter with its corresponding value as shown in the parameter file.
• Save the file with the .par extension.

How do you execute the parameter file?

At the command prompt type sqlldr PARFILE = <parfile name>.

2) The Control File:

• The control file is a text file written in a language that SQL*Loader understands.
• The control file describes the task that the SQL*Loader is to carry out. The control file tells SQL*Loader where to find the data, how to parse and interpret the data, where to insert the data, and more.
• It also contains the names and locations of the bad file and the discard file.
• Some of above information (such as name and location of the input file) can also be passed to SQL*Loader as command-line parameters.
• It’s also possible for the control file to contain the actual data to be loaded. This is sometimes done when small amounts of data need to be distributed to many sites, because it reduces (to just one file) the number of files that need to be passed around.

 A sample control file is given below:

In general, the control file has three main sections, in the following order:

a) Session-wide information:

The session-wide information contains the names of the input/output files for the data load session. Apart from this, other SQL*Loader parameters can also be listed in this section.

• The LOAD DATA statement is required at the beginning of the control file.
• INFILE * specifies that the data is found in the control file and not in an external data file.
• BADFILE ‘example1.bad’ indicates that all erroneous records must be stored in the file example1.bad.
• DISCARDFILE ‘example1.dsc’ indicates that all discarded records must be stored in the file example1.dsc.

b) Table and Field_List Information:

The INTO TABLE statement specifies the table into which data should be loaded. In this case it is the dept table. By default, SQL*Loader requires the table to be empty before it inserts any records.

FIELDS TERMINATED BY specifies that the data is terminated by commas, but can also be enclosed by quotation marks. Data types for all fields default to CHAR.

The names of columns to load are enclosed in parentheses. Because no data type or length is specified, the default is type CHAR with a maximum length of 255.

c) Input Data:

BEGINDATA specifies the beginning of the data. The data to be loaded is present below the BEGINDATA command.

Continue>>>