"You may find it helpful to
use the EXTERNAL_TABLE=GENERATE_ONLY parameter in SQL*Loader to get the
proper access parameters for a given SQL*Loader control file.
When you specify
GENERATE_ONLY, all the SQL statements needed to do the load using external
tables, as described in the control file, are placed in the SQL*Loader log
file.
These SQL statements can be
edited and customized. The actual load can be done later without the use of
SQL*Loader by executing these statements in SQL*Plus."
When you use the external
tables feature, you can place all of the SQL commands needed to do the
load, as described in the control file, in the SQL*Loader log file. To
do this, set the
EXTERNAL_TABLE parameter to
GENERATE_ONLY.
The actual load can be done later without the use of SQL*Loader by
executing these statements in SQL*Plus.
To generate an example of the log file created when using
EXTERNAL_TABLE=GENERATE_ONLY,
execute the following command for case study 1 (Case
Study 1: Loading Variable-Length Data):
sqlldr scott/tiger ulcase1 EXTERNAL_TABLE=GENERATE_ONLY
The resulting log file looks as follows:
Control File: ulcase1.ctl
Data File: ulcase1.ctl
Bad File: ulcase1.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: External Table
Table DEPT, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO FIRST * , O(") CHARACTER
DNAME NEXT * , O(") CHARACTER
LOC NEXT * , O(") CHARACTER
CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/rdbms/demo'
CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE SYS_SQLLDR_X_EXT_DEPT
(
DEPTNO NUMBER(2),
DNAME CHAR(14),
LOC CHAR(13)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000:ulcase1.bad'
SKIP 20
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
(
DEPTNO CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
DNAME CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
LOC CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'ulcase1.ctl'
)
)REJECT LIMIT UNLIMITED
INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO DEPT
(
DEPTNO,
DNAME,
LOC
)
SELECT
DEPTNO,
DNAME,
LOC
FROM SYS_SQLLDR_X_EXT_DEPT
statements to clean up objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE SYS_SQLLDR_X_EXT_DEPT
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
|
|
 |
|
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.
|
|
|
