SQL*Loader loads data from external files into tables of an Oracle database. A typical SQL*Loader session takes as input a control file, which controls the behavior of SQL*Loader, and one or more data files. The output of SQL*Loader is an Oracle database (where the data is loaded), a log file, a bad file, and potentially, a discard file.

The tables to be loaded must already exist in the database. SQL*Loader never creates tables. It loads existing tables that either already contain data or are empty.

The following privileges are required for a load:

• You must have INSERT privileges on the table to be loaded.
• You must have DELETE privileges on the table to be loaded, when using the REPLACE or TRUNCATE option to empty old data from the table before loading the new data in its place.

SQL*Loader can be used for:-

• Load data from multiple data files during the same load session.
• Load data into multiple tables during the same load session.
• Specify the character set of the data.
• Selectively load data (you can load records based on the records' values).
• Manipulate the data before loading it, using SQL functions.
• Generate unique sequential key values in specified columns.
• Use the operating system's file system to access the data files.
• Load data from disk, tape, or named pipe.
• Generate sophisticated error reports, which greatly aids troubleshooting.
• Load arbitrarily complex object-relational data.
• Use secondary data files for loading LOBs and collections.
• Use either conventional or direct path loading.
• Use a DB2 Load Utility control file as a SQL*Loader control file with few or no changes involved.

SQL*Loader Control File

The control file is a text file written in a language that SQL*Loader understands. The control file tells SQL*Loader where to find the data, how to parse and interpret the data, where to insert the data etc.

• The syntax is free-format (statements can extend over multiple lines).
• It is case insensitive; however, strings enclosed in single or double quotation marks are taken literally, including case.
• In control file syntax, comments extend from the two hyphens (--) that mark the beginning of the comment to the end of the line
• The CONSTANT keyword has special meaning to SQL*Loader and is therefore reserved.

Sample Control File

-- This is a sample control file

LOAD DATA
INFILE 'sample.dat'
BADFILE 'sample.bad'
DISCARDFILE 'sample.dsc'
APPEND
INTO TABLE emp
WHEN (57) = '.'
TRAILING NULLCOLS
(hiredate SYSDATE,
deptno POSITION(1:2) INTEGER EXTERNAL(2)
NULLIF deptno=BLANKS,
job POSITION(7:14) CHAR TERMINATED BY WHITESPACE
NULLIF job=BLANKS "UPPER(:job)",
mgr POSITION(28:31) INTEGER EXTERNAL
TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
ename POSITION(34:41) CHAR
TERMINATED BY WHITESPACE "UPPER(:ename)",
empno POSITION(45) INTEGER EXTERNAL
TERMINATED BY WHITESPACE,
sal POSITION(51) CHAR TERMINATED BY WHITESPACE
"TO_NUMBER(:sal,'$99,999.99')",
comm INTEGER EXTERNAL ENCLOSED BY '(' AND '%'
":comm * 100"
)

The LOAD DATA statement tells SQL*Loader that this is the beginning of a new data load. The INFILE clause specifies the name of a datafile containing data that you want to load.The BADFILE parameter specifies the name of a file into which rejected records are placed. The DISCARDFILE parameter specifies the name of a file into which discarded records are placed.

The APPEND parameter is one of the options you can use when loading data into a table that is not empty. To load data into a table that is empty, you would use the INSERT parameter. The INTO TABLE clause allows you to identify tables, fields, and datatypes. It defines the relationship between records in the datafile and tables in the database.

The WHEN clause specifies one or more field conditions. SQL*Loader decides whether or not to load the data based on these field conditions. The TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.

The remainder of the control file contains the field list, which provides information about column formats in the table being loaded.

If the data is included in the control file itself, then the INFILE clause is followed by an asterisk rather than a filename. The actual data is placed in the control file after the load configuration specifications. Specify the BEGINDATA parameter before the first data record. The syntax is:

BEGINDATA

Data

When using the BEGINDATA parameter:-

• If you omit the BEGINDATA parameter but include data in the control file, SQL*Loader tries to interpret your data as control information and issues an error message. If your data is in a separate file, do not use the BEGINDATA parameter.

• Do not use spaces or other characters on the same line as the BEGINDATA parameter, or the line containing BEGINDATA will be interpreted as the first line of data.

• Do not put comments after BEGINDATA, or they will also be interpreted as data.

Specifying the Datatype of a Data Field in a control file

POSITION specifies the position of a data field. The data type specification of a field tells SQL*Loader how to interpret the data in the field. For example, a data type of INTEGER specifies binary data, while INTEGER EXTERNAL specifies character data that represents a number. A CHAR field can contain any character data.

Only one data type can be specified for each field; if a datatype is not specified, CHAR is assumed.

Input Data and Datafiles

SQL*Loader reads data from one or more files (or operating system equivalents of files) specified in the control file. From SQL*Loader's perspective, the data in the datafile is organized as records. To specify a datafile that contains the data to be loaded, use the INFILE clause, followed by the filename and optional file processing options string. You can specify multiple files by using multiple INFILE clauses.

Examples of INFILE Syntax

The following list shows different ways you can specify INFILE syntax:

• Data contained in the control file itself:

INFILE *

• Data contained in a file named foo with a default extension of .dat:

INFILE foo

• Data contained in a file named datafile.dat with a full path specified:

INFILE 'c:/topdir/subdir/datafile.dat'

Discarded and Rejected Records

Records read from the input file might not be inserted into the database. Such records are placed in either a bad file or a discard file.

The Bad File

The bad file contains records that were rejected, either by SQL*Loader or by the Oracle database server. Some of the possible reasons for rejection are discussed in the next sections.

SQL*Loader Rejects

Datafile records are rejected by SQL*Loader when the input format is invalid. For example, if the second enclosure delimiter is missing, or if a delimited field exceeds its maximum length, SQL*Loader rejects the record. Rejected records are placed in the bad file.

Oracle Rejects

After a datafile record is accepted for processing by SQL*Loader, it is sent to the Oracle database server for insertion into a table as a row. If the Oracle database server determines that the row is valid, then the row is inserted into the table. If the row is determined to be invalid, then the record is rejected and SQL*Loader puts it in the bad file.

Specifying the Bad File

When SQL*Loader executes, it can create a file called a bad file or reject file in which it places records that were rejected because of formatting errors or because they caused Oracle errors. If you have specified that a bad file is to be created, the following applies:

• If one or more records are rejected, the bad file is created and the rejected records are logged.
• If no records are rejected, then the bad file is not created. When this occurs, you must reinitialize the bad file for the next run.
• If the bad file is created, it overwrites any existing file with the same name; ensure that you do not overwrite a file you wish to retain.

The Discard File

This file is created only when it is needed, and only if you have specified that a discard file should be enabled. The discard file contains records that were filtered out of the load because they did not match any record-selection criteria specified in the control file.

Specifying the Discard File

During SQL*Loader execution, it can create a discard file for records that do not meet any of the loading criteria. The records contained in this file are called discarded records. Discarded records do not satisfy any of the WHEN clauses specified in the control file. These records differ from rejected records. Discarded records do not necessarily have any bad data. No insert is attempted on a discarded record.

A discard file is created according to the following rules:

• You have specified a discard filename and one or more records fail to satisfy all of the WHEN clauses specified in the control file. (If the discard file is created, it overwrites any existing file with the same name, so be sure that you do not overwrite any files you wish to retain.)
• If no records are discarded, then a discard file is not created

Log File and Logging Information

When SQL*Loader begins execution, it creates a log file. If it cannot create a log file, execution terminates. The log file contains a detailed summary of the load, including a description of any errors that occurred during the load. Most of the log file entries are records of successful SQL*Loader execution. However, errors can also cause log file entries. For example, errors found during parsing of the control file appear in the log file.

Conventional Path Loads

During conventional path loads, the input records are parsed according to the field specifications, and each data field is copied to its corresponding bind array. When the bind array is full (or no more data is left to read), an array insert is executed.

Direct Path Loads

A direct path load parses the input records according to the field specifications, converts the input field data to the column datatype, and builds a column array. The column array is passed to a block formatter, which creates data blocks in Oracle database block format. The newly formatted database blocks are written directly to the database, bypassing most RDBMS processing. Direct path load is much faster than conventional path load, but entails several restrictions.

How to run SQL LOADER

The control file is ulcase1.ctl:

LOAD DATA
INFILE *
INTO TABLE dept
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(deptno, dname, loc)
BEGINDATA
12,RESEARCH,"SARATOGA"
10,"ACCOUNTING",CLEVELAND
11,"ART",SALEM
13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT'L","SAN FRAN"