Oracle Utilities Tips
SQL*Loader () is the
utility to use for high performance data loads. The data can
be loaded from any text file and inserted into the database.
Figure 4.2 depicts the SQL*Loader
architecture. SQL*Loader reads a data file and a description
of the data which is defined in the control file. Using this
information and any additional specified parameters (either on
the command line or in the PARFILE), SQL*Loader loads the data
into the database.
During processing, SQL*Loader writes
messages to the log file, bad rows to the bad file, and
discarded rows to the discard file.
Figure 4.2 – SQL*Loader Architecture
The SQL*Loader control file contains
information that describes how the data will be loaded. It
contains the table name, column datatypes, field delimiters,
etc. It simply provides the guts for all SQL*Loader
Manually creating control files is an
error-prone process. The following SQL script () can be used
to generate an accurate control file for a given table. The
script accepts a table name and a date format (to be used for
date columns), and generates a valid control file to use with
SQL*Loader for that table.
SEE CODE DEPOT
Once executed and given a table name and
date format, controlfile.sql will generate a control
file with the following contents:
SEE CODE DEPOT
The control file can also specify that
records are in fixed format. A file is in fixed record format
when all records in a datafile are the same length. The
control file specifies the specific starting and ending byte
location of each field. This format is harder to create and
less flexible but can yield performance benefits. A control
file specifying a fixed format for the same table could look
like the following:
, COL2 POSITION(6:9)
, COL3 POSITION(11:46)
, col4 position(48:83)
, col5 position(85:120)
, COL6 POSITION(122:130)
To learn more about these techniques,
see the book "Advanced
Oracle Utilities: The Definitive Reference".
You can buy it directly from the
publisher and get instant access to the code depot
of utilities scripts.