SQL*Loader
SQL*Loader is a tool that loads external data into oracle database
tables.
Basic SQL*Loader Components
SQL*Loader requires two types of input: The external data , which
can reside on disk or tape , and control information (contained in the
control file) ,
which describes the characteristics
of the input data and the tables and columns to load . The outputs, some
of which are optional , include the Oracle tables,
log file , bad files and discard
files.
The Input Data
SQL*Loader can process practically any type of data file , and it
supports native datatypes for almost any platform. Data is usually read
from one or
more data files. However,
data can also be embedded in the control file after the control information
.
The Control File
Before SQL*Loader can process the data in the data files , you must define
the data specifications to the SQL*Loader. You use the control file to
define
the physical data
file specifications , as well as the format of the data in the files.
The Log File
Upon Execution , SQL*Loader creates a log file containing detailed information about the load , including these items :
-> Name of the input data files , control file , bad files , and discard
files
-> Input data and associated table specifications
-> SQL*Loader errors
-> SQL*Loader results
->Summary statistics
Discard and Bad Files
SQL*Loader has the built-in functionality , through specifications in the
control file , to format the input data and include or exclude the input
record
based on record-selection
criteria. If SQL*Loader includes the record for processing, it is passed
to the Oracle kernel for insertion into the database tables.
If the SQL*Loader
rejects the record due to a format error, or the Oracle kernel cannot
insert the record into the databse tables for any reason, the input
record is written
to the BAD file in the same format as the original input data file. If
SQL*Loader rejects the record due to an exclusion based on the record
selection criteria,
the input record is written to the DISCARD file ( provided it is specified
in the control file) , also in the same format as the original input
data file. Because
the BAD and DISCARD files are written in the same format as the orignal
input data file, they can be edited , if necessary , and used as
input data files
to another SQL*Loader session.
Simple Example :
File "components.sql" : CREATE TABLE COMPONENTS
(
TERM VARCHAR2(3) NOT NULL,
LINENO NUMBER(4) NOT NULL,
COMPNAME VARCHAR2(15) NOT NULL,
MAXPOINTS NUMBER(4),
WEIGHT NUMBER(2) );
SQL> START COMPONENTS.SQL
// Table created
File "components.ctl" : load
data
infile "components.txt"
into table components
fields terminated by "}"
( term,lineno,compname,maxpoints,weight)
File "components.txt" : F96}1031}EXAM1}100}30}
F96}1031}QUIZZES}80}20}
F96}1031}FINAL}100}50}
F96}1032}PROGRAMS}400}40}
F96}1032}MIDTERM}100}20}
F96}1032}FINAL}100}40}
To Load Data :
dbs1 % sqlload username/password control=components.ctl
//data loaded..log file created ..