External Tables - Oracle 10g/11g Data and Database Management Utilities - page 63

• GENERATE_ONLY : If this option is specified, then SQL*Loader will only read
the definitions from the control file and generate the required commands,
so the user can record them for later execution, or tailor them to fit his/her
particular needs.
• EXECUTE : This not only generates the External Table scripts, but also executes
them. If the user requires a sequence, then the EXECUTE option will not only
create the table, but it will also create the required sequence, deleting it once
the data load is finished. This option performs the data load process against
the specified target regular by means of an External Table, it creates both
the directory and the External Table, and inserts the data using a SELECT AS
INSERT with the APPEND hint.
Let's use the GENERATE_ONLY option to generate the External Table creation scripts:
$ sqlldr exttabdemo/oracle employees external_table=GENERATE_ONLY
By default the log file is located in a file whose extension is .log and its name
equals that of the control file. By opening it we see, among the whole log processing
information, this set of DDL commands:
CREATE TABLE "SYS_SQLLDR_X_EXT_EMPLOYEES"
(
"EMPLOYEE_ID" NUMBER(6),
"FIRST_NAME" VARCHAR2(20),
"LAST_NAME" VARCHAR2(25),
"EMAIL" VARCHAR2(25),
"PHONE_NUMBER" VARCHAR2(20),
"HIRE_DATE" DATE,
"JOB_ID" VARCHAR2(10),
"SALARY" NUMBER(8,2),
"COMMISSION_PCT" NUMBER(2,2),
"MANAGER_ID" NUMBER(6),
"DEPARTMENT_ID" NUMBER(4)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY EXTTABDIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'EXTTABDIR':'employees.bad'
LOGFILE 'employees.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM