"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
