Sqlldr External Option

sqlldr … external_table=…
can generate external table from sqlldr control file.
the available values of external_table are NOT_USE, GENERATE_ONLY, EXEUTE.

NOT_USE	disable the function
GENERATE_ONLY	check the function and create the script
EXEUTE	load data with external table

Example

empxt1.dat

360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus
361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper
362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr
363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aaldag

empxt1.ctl

LOAD DATA
INFILE 'empxt1.dat'
badfile 'empxt1.bad'
APPEND
INTO TABLE employees_ldr
fields terminated by ','
         ( employee_id, first_name, last_name, job_id, manager_id,
           hire_date date "dd-mon-yyyy",
           salary, commission_pct, department_id, email
         )

generate external table script with sqlldr

sqlldr hr/hr control=empxt1.ctl external_table=GENERATE_ONLY

SQL*Loader: Release 10.2.0.1.0 - Production on Sun Nov 1 16:43:38 2009
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
Control File:   empxt1.ctl
Data File:      empxt1.dat
  Bad File:     empxt1.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 EMPLOYEES_LDR, loaded from every logical record.
Insert option in effect for this table: APPEND
 
   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPLOYEE_ID                         FIRST     *   ,       CHARACTER
FIRST_NAME                           NEXT     *   ,       CHARACTER
LAST_NAME                            NEXT     *   ,       CHARACTER
JOB_ID                               NEXT     *   ,       CHARACTER
MANAGER_ID                           NEXT     *   ,       CHARACTER
HIRE_DATE                            NEXT     *   ,       DATE dd-mon-yyyy
SALARY                               NEXT     *   ,       CHARACTER
COMMISSION_PCT                       NEXT     *   ,       CHARACTER
DEPARTMENT_ID                        NEXT     *   ,       CHARACTER
EMAIL                                NEXT     *   ,       CHARACTER
 
CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_EMPLOYEES_LDR"
(
  "EMPLOYEE_ID" NUMBER(4),
  "FIRST_NAME" VARCHAR2(20),
  "LAST_NAME" VARCHAR2(25),
  "JOB_ID" VARCHAR2(10),
  "MANAGER_ID" NUMBER(4),
  "HIRE_DATE" DATE,
  "SALARY" NUMBER(8,2),
  "COMMISSION_PCT" NUMBER(2,2),
  "DEPARTMENT_ID" NUMBER(4),
  "EMAIL" VARCHAR2(25)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY ADMIN_DAT_DIR
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'ADMIN_LOG_DIR':'empxt1.bad'
    LOGFILE 'empxt1.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," LDRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "EMPLOYEE_ID" CHAR(255)
        TERMINATED BY ",",
      "FIRST_NAME" CHAR(255)
        TERMINATED BY ",",
      "LAST_NAME" CHAR(255)
        TERMINATED BY ",",
      "JOB_ID" CHAR(255)
        TERMINATED BY ",",
      "MANAGER_ID" CHAR(255)
        TERMINATED BY ",",
      "HIRE_DATE" CHAR(255)
        TERMINATED BY ","
        DATE_FORMAT DATE MASK "dd-mon-yyyy",
      "SALARY" CHAR(255)
        TERMINATED BY ",",
      "COMMISSION_PCT" CHAR(255)
        TERMINATED BY ",",
      "DEPARTMENT_ID" CHAR(255)
        TERMINATED BY ",",
      "EMAIL" CHAR(255)
        TERMINATED BY ","
    )
  )
  location
  (
    'empxt1.dat'
  )
)REJECT LIMIT UNLIMITED
 
INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO EMPLOYEES_LDR
(
  EMPLOYEE_ID,
  FIRST_NAME,
  LAST_NAME,
  JOB_ID,
  MANAGER_ID,
  HIRE_DATE,
  SALARY,
  COMMISSION_PCT,
  DEPARTMENT_ID,
  EMAIL
)
SELECT
  "EMPLOYEE_ID",
  "FIRST_NAME",
  "LAST_NAME",
  "JOB_ID",
  "MANAGER_ID",
  "HIRE_DATE",
  "SALARY",
  "COMMISSION_PCT",
  "DEPARTMENT_ID",
  "EMAIL"
FROM "SYS_SQLLDR_X_EXT_EMPLOYEES_LDR"
 
statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_EMPLOYEES_LDR"
 
Run began on Sun Nov 01 16:43:38 2009
Run ended on Sun Nov 01 16:43:38 2009
 
Elapsed time was:     00:00:00.08
CPU time was:         00:00:00.03