Introduction
SQL*Loader is a bulk loader
utility used for moving data from external files into the Oracle database.
Capabilities
of SQL*Loader
•
Can
load data from multiple data files during the same load session.
•
Can
load data into multiple tables during the same load session.
•
Can
selectively load data (Records can be loaded based on the records' values).
•
Can
manipulate the data before loading it, using SQL functions.
•
Can
generate unique sequential key values in specified columns .
Does
sophisticated error reporting which greatly aids troubleshooting
Other
useful posts on sql loader:
SQL Loader - How it works
SQL*Loader takes as input a
control file, which controls the behavior of SQL*Loader, and one or more data
files.
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.
Control
File : The SQL*Loader control File is the key to any load process.
The
control file provides the following information to SQL*Loader.
•
The
name and location of the input file
•
The
format of the records in the input data file
•
The
name of table or tables to be loaded
•
The
correspondence between the fields in the input record and the columns in the
database tables being loaded.
•
Selection
criteria defining which records from the input file contain data to be inserted
into the destination database tables
•
The
names and location of the bad file and the discard file.
Data
File : The data file contains the data to be loaded in a specific
format.
Sometimes
if the amount of data is less, it can be embedded as part of the control file.
Bad
File : The bad file contains records rejected, either by SQL*Loader or
by Oracle.
Records
are rejected by SQL*Loader when the input format is invalid.
For
example: If a delimited field exceeds the specified length.
After
a record is accepted for processing by SQL*Loader, a row is sent to Oracle for
insertion.
If
Oracle determines that the row is valid, then the row is inserted into the
database.
If
not, the record is rejected, and SQL*Loader puts it in the bad file.
The
row may be rejected, for example, because a key is not unique, because a
required field is not null, or because the field contains invalid data for the
Oracle data type.
Log
File : The log file contains a detailed summary of the load, including a
description of any errors that occurred during the load.
The
summary of the load includes the number of logical records read from the data
file, the number of records rejected because of errors, the number of records
discarded because of selection criteria, and the elapsed time of the load.
Discard
File : 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.
Discard
file is created only when it is needed, and only if specified that a discard
file should be enabled.
Structure of control file
LOAD DATA
INFILE ‘<data file name>’
BADFILE ‘<bad file name>’
DISCARDFILE ‘<discard file name>’
APPEND/TRUNCATE/REPLACE/INSERT
INTO TABLE <table name>
WHEN <condition>
TRAILING NULLCOLS
(column 1
constant,
column 2
<sequence_name>.nextval,
column 3
“<sql function(data field)>”,
…)
<BEGINDATA>
•
LOAD DATA : It signifies the start of control file.
•
INFILE : Specifies the Data file name(s)
•
BADFILE : Specifies Bad file name that gets
created during the SQL loader run
•
DISCARDFILE: Specifies the Discard file name that gets
created
•
APPEND/TRUNCATE/INSERT/REPLACE : Operation that
will be performed on the database table
•
INTO TABLE: Table name to which the data will be
loaded
•
BEGINDATA: Specifies that the subsequent lines of
the control file contains data. In this case INFILE use is not required
•
POSITION: Specifies the position of each column of
data
•
WHEN : Contains the condition(s)
Types of Data files
Fixed
length Data files
All the records in the data file are of
the same byte length
Fields are identified by their positions
in the records.
Example:
Emp No : 1-5
Emp Name : 6-16
Department Name : 17-21
111 JOHN
SALES
222 MARK
HR
333 NANCY
MARKETING
Variable
length Data files
Columns in the input data file have
variable lengths
Fields are identified by a delimiter
Example:
In the following example, a comma separates the input values
111,JOHN,SALES
222,MARK,HR
333,NANCY,MARKETING
The
fields terminated by “,” clause tells SQL*Loader that during the load, each
column value will be terminated by a comma
Load Processing:
A
control file is executed by using SQLLDR command at the operating system
prompt.
Some
input parameters should be given for executing the control file.
Each
load must have an control file.
Syntax
: sqlldr <apps user name>/<apps pwd> control=<control file
name> log=<log file name> data=<data file name>
