External Table Syntax from SQL*Loader

Earlier in this article, a number of advantages of external tables over SQL*Loader were listed. It was also noted that in some cases we might use external tables as a replacement for SQL*Loader but continue to use staging tables. A new feature of SQL*Loader helps us to develop this quickly; this is the EXTERNAL_TABLE option. This option generates an external table "load" script from an existing SQL*Loader control file. Once we have this, the script can be cleaned and modified as required. For existing loads, this dramatically removes the development time of external tables and removes much of the time spent learning the new syntax through the inevitable "trial and error" phases with the access parameters.

In the following example, we'll create a small SQL*Loader control file to load a staging table named EMP_STG. We will use the new option EXTERNAL_TABLE=GENERATE_ONLY in the control file and run it to generate a SQL script. 

options (external_table=generate_only)
load data
infile 'd:\oracle\dir\emp.dat'
badfile 'd:\oracle\dir\emp.bad'
truncate
into table emp_stg
fields terminated by ','
trailing nullcols
(
  empno
, ename
, job
, mgr
, hiredate date "dd/mm/yyyy"
, sal
, comm
, deptno
)

Note that invoking SQL*Loader for this file will not load any data. First, we can see the control file. Note the options clause. The external_table=generate_only clause makes SQL*Loader run "silently" and generate a logfile only. SQL*Loader is invoked as normal. The resulting logfile contains the following SQL statements based on the contents of the control file. These statements support an external table load to replace our SQL*Loader job.

CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'd:\oracle\dir\'


CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_EMP_STG"
(
  EMPNO NUMBER(4),
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(2)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'emp.bad'
    LOGFILE 'emp.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
      EMPNO CHAR(255)
        TERMINATED BY ",",
      ENAME CHAR(255)
        TERMINATED BY ",",
      JOB CHAR(255)
        TERMINATED BY ",",
      MGR CHAR(255)
        TERMINATED BY ",",
      HIREDATE CHAR(255)
        TERMINATED BY ","
        DATE_FORMAT DATE MASK "dd/mm/yyyy",
      SAL CHAR(255)
        TERMINATED BY ",",
      COMM CHAR(255)
        TERMINATED BY ",",
      DEPTNO CHAR(255)
        TERMINATED BY ","
    )
  )
  location
  (
    'emp.dat'
  )
)REJECT LIMIT UNLIMITED


INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO EMP_STG
(
  EMPNO,
  ENAME,
  JOB,
  MGR,
  HIREDATE,
  SAL,
  COMM,
  DEPTNO
)
SELECT
  EMPNO,
  ENAME,
  JOB,
  MGR,
  HIREDATE,
  SAL,
  COMM,
  DEPTNO
FROM "SYS_SQLLDR_X_EXT_EMP_STG"


statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_EMP_STG"

DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

Oracle has done the work for us and provided a script to create our EMP_XT external table. We will probably choose to clean up certain elements of the generated code (such as the object names, for example), but the hard work of converting a SQL*Loader load to an external table load is done.