SQL*Loader vs External Tables

Behavior differences between SQL*Loader and External Tables

This section describes important differences between loading data with external tables, using the ORACLE_LOADER access driver, as opposed to loading data with SQL*Loader conventional and direct path loads. This information does not apply to the ORACLE_DATAPUMP access driver.

Multiple Primary Input Datafiles

If there are multiple primary input datafiles with SQL*Loader loads, a bad file and a discard file are created for each input datafile. With external table loads, there is only one bad file and one discard file for all input datafiles. If parallel access drivers are used for the external table load, each access driver has its own bad file and discard file.

Syntax and Datatypes

The following are not supported with external table loads:

• Use of CONTINUEIF or CONCATENATE to combine multiple physical records into a single logical record.
• Loading of the following SQL*Loader datatypes: GRAPHIC, GRAPHIC EXTERNAL, and VARGRAPHIC
• Use of the following database column types: LONGs, nested tables, VARRAYs, REFs, primary key REFs, and SIDs

Byte-Order Marks

With SQL*Loader, if a primary datafile uses a Unicode character set (UTF8 or UTF16) and it also contains a byte-order mark (BOM), then the byte-order mark is written at the beginning of the corresponding bad and discard files. With external table loads, the byte-order mark is not written at the beginning of the bad and discard files.

Default Character Sets and Date Masks

For fields in a datafile, the settings of NLS environment variables on the client determine the default character set and date masks. For fields in external tables, the setting of NLS environment variables on the server determine the default character set and date masks.

Use of the Backslash Escape Character

In SQL*Loader, you can use the backslash (\) escape character to mark a single quotation mark as a single quotation mark, as follows:

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\''

In external tables, the use of the backslash escape character within a string will raise an error. The workaround is to use double quotation marks to mark the separation string, as follows:

TERMINATED BY ',' ENCLOSED BY "'"

You can use special syntax with SQL*Loader (sqlldr) to create the definitions for external tables with the EXTERNAL_TABLE=GENERATE_ONLY parameters.

The Oracle Utilities document notes:

"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