SQL*Loader loads data from external files into tables of an Oracle
database. It has a powerful data parsing engine that puts little limitation on
the format of the data in the data file.
SQL*Loader uses the following files:
Input data files: SQL*Loader reads data from one or more files (or
operating system equivalents of files) that are specified in the control file.
From SQL*Loader’s perspective, the data in the data file is organized as
records. A particular data file can be in fixed record format, variable record
format, or stream record format. The record format can be specified in the
control file with the INFILE parameter. If no record format is specified, the
default is stream record format.
Control file: The control file is a text file that is written in a
language that SQL*Loader understands. The control file indicates to SQL*Loader
where to find the data, how to parse and interpret the data, where to insert
the data, and so on. Although not precisely defined, a control file can be said
to have three sections.
•
The first section contains such session-wide
information as the following:
•
Global options, such as the input data file name and
records to be skipped
•
INFILE clauses to specify where the input data is located
•
Data to be loaded
•
The
second section consists of one or more INTO TABLE blocks. Each of these blocks
contains information about the table (such as the table name and the columns of
the table) into which the data is to be loaded.
•
The
third section is optional and, if present, contains input data.
Log
file: 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.
Bad
file: The bad file
contains records that are rejected, either by SQL*Loader or by the Oracle
database. Data file records are rejected by SQL*Loader when the input format is
invalid. After a data file record is accepted for processing by SQL*Loader, it
is sent to the Oracle database for insertion into a table as a row. If the
Oracle database determines that the row is valid, the row is inserted into the
table. If the row is determined to be invalid, the record is rejected and
SQL*Loader puts it in the bad file.
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 are filtered out
of the load because they do not match any record-selection criteria specified
in the control file.
Loading
Data with SQL*Loader
Use the
“Load Data from User Files Wizard” to load data from a flat file into an Oracle
database.
To display
the wizard, select Enterprise Manager Data Movement > Move Row Data >
Load Data from User Files.
SQL*Loader
Control File
The
SQL*Loader control file is a text file that contains data definition language
(DDL) instructions. DDL is used to control the following aspects of a
SQL*Loader session:
•
Where
SQL*Loader finds the data to load
•
How
SQL*Loader expects that data to be formatted
•
How
SQL*Loader is being configured (including memory management, selection and
rejection criteria, interrupted load handling, and so on) as it loads the data
•
How
SQL*Loader manipulates the data being loaded
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(3)
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
explanation of this sample control file (by line numbers) is as follows:
1. Comments can appear anywhere in the
command section of the file, but they must not appear in the data. Precede any
comment with two hyphens. All text to the right of the double hyphen is ignored
until the end of the line.
2. The LOAD DATA statement indicates to
SQL*Loader that this is the beginning of a new data load. If you are continuing
a load that has been interrupted in progress, use the CONTINUE LOAD DATA
statement.
3. The INFILE keyword specifies the name of
a data file containing data that you want to load.
4. The BADFILE keyword specifies the name of
a file into which rejected records are placed.
5. The DISCARDFILE keyword specifies the
name of a file into which discarded records are placed.
6. The APPEND keyword is one of the options
that you can use when loading data into a table that is not empty. To load data
into a table that is empty, use the INSERT keyword.
7. The INTO TABLE keyword enables you to
identify tables, fields, and data types. It defines the relationship between
records in the data file and tables in the database.
8. The WHEN clause specifies one or more
field conditions that each record must match before SQL*Loader loads the data.
In this example, SQL*Loader loads the record only if the 57th character is a
decimal point. That decimal point delimits dollars and cents in the field and
causes records to be rejected if SAL has no value.
9. The TRAILING NULLCOLS clause prompts
SQL*Loader to treat any relatively positioned columns that are not present in
the record as null columns.
10. The remainder of the control file contains
the field list, which provides information about column formats in the table
that is being loaded.
