 |
|
SQL*Loader - loading data into multiple tables
Oracle Database Tips by Donald Burleson |
Question: I want to
load data into multiple tables using SQL*Loader, but I don't
understand the syntax of the control file for directing data into
many different tables. Can you load many tables with
SQL*Loader?
Answer: The Oracle docs note that
sqlldr allows multiple "into table" clauses. Dave Moore has working
examples of complex SQL*Loader control files in the code depot for his book "Oracle
Utilities", a highly recommended reference for sqlldr.
Here is an example of a SQL*Loader controlfile
to load data into multiple tables:
LOAD DATA
INFILE /u01/app/oracle/load.dat
REPLACE
INTO TABLE emp
WHEN empno != ' '
( empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR
)
INTO TABLE proj
WHEN projno != ' '
( projno POSITION(25:27) INTEGER EXTERNAL,
empno POSITION(1:4) INTEGER EXTERNAL
)
The Oracle documentation notes:
Multiple INTO TABLE clauses allow you to:
- Load data into different tables
- Extract multiple logical records from a single input record
- Distinguish different input record formats
- Distinguish different input row object subtypes
In the first case, it is common for the
INTO TABLE clauses to refer to the same table. This section illustrates the
different ways to use multiple INTO TABLE clauses and shows you how to use
the POSITION parameter.
A key point when using multiple INTO TABLE clauses is that field scanning
continues from where it left off when a new INTO TABLE clause is processed.
The remainder of this section details important ways to make use of that
behavior. It also describes alternative ways using fixed field locations or
the POSITION parameter.
|
|
 |
|
Get the Complete
Oracle Utility Information
The landmark book
"Advanced Oracle
Utilities The Definitive Reference" contains over 600 pages of
filled with valuable information on Oracle's secret utilities.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
|
|
