Sql*Loader 12.1 Express mode

Loading Data Using Sql*Loader Express Load mode.

Sql*Loader is Oracle’s high speed data loading utility. But, when working with Sql*Loader, it has always been necessary to first create a control file for the utility to work with.

Creating the loader control file requires getting the syntax, column positions, etc correct, and it can be an error prone process.

Starting Oracle Database 12c, Sql*Loader supports an Express Load mode, which does not require a loader control file to do simple loads.

Express Load mode eases the task of loading the data into the database, enables automatic parallelism and avoids need to split the input into multiple files for parallel loads.

There are a couple of conditions for express load mode to work

1. The input data columns are simple data types – text, date-time, number only.
2. The input data columns are in the same order as they appear in the table.
3. The control file parameter should not be specified at the command line.

In the Express Load mode, the user simply invokes the loader and specifies a table name and user credentials. For example to load a text file called employees.dat into the table hr.employees in the database called noncdb1:

$\> sqlldr hr/hr@localhost:1521/noncdb1 table=employees

Because the control file parameter was not specified, Sql*Loader will launch in express load mode, and look for a file called employees.dat.

In this mode, Sql*Loader will look up the target table, structure, and write out a loader control file possible for future use in its log file, assuming:

1. The input file columns are comma-delimited.

2. Each record is new-line-terminated.

3. Input file column order is the same as employees table.

4. The user intended to append the existing table.

Sql*Loader will also attempt an external table mode load of the data.

1. Create a temp external table based on the input file.

2. Generate a script to do an INSERT INTO target select * from <temp-external-table>

3. Perform the insert.

The default load method for the express load option is the external table method.

Sql*Loader can also load data using one of these methods

§        Direct path load.

§        Conventional path load.

In Express Load mode, Sql*Loader will need a directory object for external table load, which points to the input file. It first looks at existing directory objects to see if there are any which point to the input file location, and if not will try to create a temporary one.

If the user who invoked Sql*Loader does not have the necessary privileges, then the directory creation fails, and Sql*Loader will fall back to using the direct path load and still be able to load the input file.

Below is an example of a Express Load mode to load a simple employees table. Consider an employees table which is being loaded, and has this structure.

SQL> desc employees

Name       Type         Nullable Default Comments

---------- ------------ -------- ------- --------

ID         NUMBER(5)    Y                        

NAME       VARCHAR2(20) Y                        

START_DATE DATE         Y                         

EMPNO      NUMBER(5)    Y                        

A text input file containing employee data records in comma-delimited format is available, containing about 1000 records. Its name is employees.dat

[oracle@laboms ~]$ cd /home/oracle

[oracle@laboms ~]$ ls -l employees.dat

-rwxr-xr-x 1 oracle oinstall 36368 Jul  9 16:03 employees.dat

The employees table is in a pluggable database named HRPDB, and belongs to the hr schema.

The loading session is started off from the $ prompt as shown.

[oracle@laboms ~]$ sqlldr hr/hr@localhost:1521/hrpdb table=employees

SQL*Loader: Release 12.1.0.1.0 - Production on Wed Jul 9 16:07:34 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Express Mode Load, Table: EMPLOYEES

Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO

Table EMPLOYEES:

  1000 Rows successfully loaded.

Check the log files:

  employees.log

  employees_%p.log_xt

for more information about the load.

[oracle@laboms ~]$

Sql*Loader assumes that the input file has the same name as the target table. The output shows that Sql*Loader started up in Express Load mode, and used the external table method to load the table. 1000 records were loaded into the employees table.

Examining the log file employees.log, we see that

1. A control file has been scripted for future use

Generated control file for possible reuse:

OPTIONS(EXTERNAL_TABLE=EXECUTE, TRIM=LRTRIM)

LOAD DATA

INFILE 'employees'

APPEND

INTO TABLE EMPLOYEES

FIELDS TERMINATED BY ","

(

  ID,

  NAME,

  START_DATE DATE,

  EMPNO

)

End of generated control file for possible reuse.

2. Further down the log file, we see that it has created an temporary directory object, and an external table pointing to the input file employees.dat

created temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for path /home/oracle

enable parallel DML: ALTER SESSION ENABLE PARALLEL DML

creating external table "SYS_SQLLDR_X_EXT_EMPLOYEES"

CREATE TABLE "SYS_SQLLDR_X_EXT_EMPLOYEES"

(

  "ID" NUMBER(5),

  "NAME" VARCHAR2(20),

  "START_DATE" DATE,

  "EMPNO" NUMBER(5)

)

ORGANIZATION external

(

  TYPE oracle_loader

  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

  ACCESS PARAMETERS

…

…

    REJECT ROWS WITH ALL NULL FIELDS

    (

      "ID" CHAR(255),

      "NAME" CHAR(255),

      "START_DATE" CHAR(255)

        DATE_FORMAT DATE MASK "DD-MON-RR",

      "EMPNO" CHAR(255)

    )

  )

  location

  (

    'employees.dat'

  )

)REJECT LIMIT UNLIMITED

3. Does a direct-path insert from the external table to the target table.

INSERT /*+ append parallel(auto) */ INTO EMPLOYEES

(

  ID,

  NAME,

  START_DATE,

  EMPNO

)

SELECT

  "ID",

  "NAME",

  "START_DATE",

  "EMPNO"

FROM "SYS_SQLLDR_X_EXT_EMPLOYEES"

4. Performs cleanup and exits. External directory is preserved (for future loads?)

dropping external table "SYS_SQLLDR_X_EXT_EMPLOYEES"

Table EMPLOYEES:

  1000 Rows successfully loaded.

Run began on Wed Jul 09 16:07:34 2014

Run ended on Wed Jul 09 16:07:37 2014

Elapsed time was:     00:00:02.50

CPU time was:         00:00:00.01

5. Next, try to retrieve the records from the table

SQL> select * from employees fetch first 10 rows only;

        ID NAME                 START_DAT      EMPNO

---------- -------------------- --------- ----------

         1 EMP_ 00000001        06-OCT-03      14211

         2 EMP_ 00000002        05-APR-04      14003

         3 EMP_ 00000003        17-NOV-02      15669

         4 EMP_ 00000004        16-JUN-00       8135

         5 EMP_ 00000005        02-OCT-03       5783

         6 EMP_ 00000006        20-MAR-05       8154

         7 EMP_ 00000007        06-MAY-02       1676

         8 EMP_ 00000008        11-DEC-01      15087

         9 EMP_ 00000009        16-MAY-04       7462

        10 EMP_ 00000010        25-MAR-00      11849

10 rows selected.