SQL Loader Express Mode - Loading data in Oracle database can't be more easy

Last weekend, I attended the Oracle Developer Community Yatra - AIOUG 2018 (awesome event, must join for DBAs and Developers). There I had a discussion with some of my database expert friends on How SQL*Loader with Oracle database 12c is back with a bang. If table structure and name are inline with the CSV file (with ".dat" extension), it is really really easy to load the data using SQL*Loader in Express Mode. SQL*Loader in Express Mode usages External Table behind the scene. With this post I am trying give an example on how easy it is to load data in Oracle Database 12c using the sqllrd utility.

So let me first create an empty table EMP2 using the structure of SCOTT.EMP

ngarg> create table emp2 as select * from scott.emp where 1=2;
Table created.

Also I have exported the data of EMP table into CSV file named EMP2.dat, which looks like following

7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20
7900,JAMES,CLERK,7698,03-DEC-81,950,,30
7934,MILLER,CLERK,7782,23-JAN-82,1300,,10

As you can notice, CSV FILE name (emp2.dat) and TABLE name (emp2) are same, the data in CSV file is in correct format as per the EMP2 table structure. Now I can use SQL*Loader in Express mode to load the data without creating any control file, and simply proving the username, password and table name.

C:\oracle122>sqlldr userid=ngarg@xe table=emp2
Password:

SQL*Loader: Release 12.2.0.1.0 - Production on Tue Jul 10 13:47:03 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Express Mode Load, Table: EMP2
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO

Table EMP2:
  14 Rows successfully loaded.

Check the log files:
  emp2.log
  emp2_%p.log_xt
for more information about the load.

As you can see sqlldr is successfully excuted and data is loaded in EMP2 table.

I quickly wants to cover what steps SQL*Loader Express performed behind the scene for us.
1. Generated control file
2. Created temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for path C:\oracle122
3. Created external table "SYS_SQLLDR_X_EXT_EMP2"
4. Excuted Insert into EMP2 select * from SYS_SQLLDR_X_EXT_EMP2
5. Dropped external table "SYS_SQLLDR_X_EXT_EMP2"

It also actually created a emp2.log file which has all the above steps, as following

SQL*Loader: Release 12.2.0.1.0 - Production on Wed Jul 11 11:19:03 2018

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

Express Mode Load, Table: EMP2
Data File:      emp2.dat
  Bad File:     emp2_%p.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 EMP2, loaded from every logical record.
Insert option in effect for this table: APPEND

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO                               FIRST     *   ,       CHARACTER            
ENAME                                NEXT     *   ,       CHARACTER            
JOB                                  NEXT     *   ,       CHARACTER            
MGR                                  NEXT     *   ,       CHARACTER            
HIREDATE                             NEXT     *   ,       DATE "DD-MON-RR"     
SAL                                  NEXT     *   ,       CHARACTER            
COMM                                 NEXT     *   ,       CHARACTER            
DEPTNO                               NEXT     *   ,       CHARACTER            

Generated control file for possible reuse:
OPTIONS(EXTERNAL_TABLE=EXECUTE, TRIM=LRTRIM)
LOAD DATA
INFILE 'emp2'
APPEND
INTO TABLE EMP2
FIELDS TERMINATED BY ","
(
  EMPNO,
  ENAME,
  JOB,
  MGR,
  HIREDATE DATE,
  SAL,
  COMM,
  DEPTNO
)
End of generated control file for possible reuse.

created temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for path C:\oracle122

enable parallel DML: ALTER SESSION ENABLE PARALLEL DML

creating external table "SYS_SQLLDR_X_EXT_EMP2"

CREATE TABLE "SYS_SQLLDR_X_EXT_EMP2" 
(
  "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 US7ASCII
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'emp2_%p.bad'
    LOGFILE 'emp2_%p.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," LRTRIM 
    REJECT ROWS WITH ALL NULL FIELDS 
    (
      "EMPNO" CHAR(255),
      "ENAME" CHAR(255),
      "JOB" CHAR(255),
      "MGR" CHAR(255),
      "HIREDATE" CHAR(255)
        DATE_FORMAT DATE MASK "DD-MON-RR",
      "SAL" CHAR(255),
      "COMM" CHAR(255),
      "DEPTNO" CHAR(255)
    )
  )
  location 
  (
    'emp2.dat'
  )
)REJECT LIMIT UNLIMITED

executing INSERT statement to load database table EMP2

INSERT /*+ append parallel(auto) */ INTO EMP2 
(
  EMPNO,
  ENAME,
  JOB,
  MGR,
  HIREDATE,
  SAL,
  COMM,
  DEPTNO
)
SELECT 
  "EMPNO",
  "ENAME",
  "JOB",
  "MGR",
  "HIREDATE",
  "SAL",
  "COMM",
  "DEPTNO"
FROM "SYS_SQLLDR_X_EXT_EMP2"

dropping external table "SYS_SQLLDR_X_EXT_EMP2"

Table EMP2:
  14 Rows successfully loaded.

Run began on Wed Jul 11 11:19:03 2018
Run ended on Wed Jul 11 11:19:52 2018

Elapsed time was:     00:00:49.22
CPU time was:         00:00:41.83

For some extra customization Oracle allows us to provide following command-line parameter with SQL Loaded in Express MODE
1. TERMINATED_BY - specifies the character that is used to terminate the fields
2. FIELD_NAMES = FIRST - data file has the field name list as its first record.
3. DATA = Different Data File
4. OPTIONALLY_ENCLOSED_BY and ENCLOSED_BY - specifies enclosure character around fields
5. BAD – the file where SQL*Loader writes records that could not be loaded.
6. CHARACTERSET – the name of the character set used to encode the data files.
7. DATE_FORMAT – the format string to use when interpreting dates in the data file.
8. DEGREE_OF_PARALLELISM – the degree of parallelism to use when loading with external tables.

My personal view on SQL*Loader in Express Mode with Oracle database 12c, It can't be more easy than this, I loaded data in Oracle Database with just one parameter and no control file, just Awesome !

Related Posts:-
- Top 18 features of Oracle 18c
- Oracle Database 12c R2 - New Features for Developers
- Oracle Database 12c - New Features for Developers
- Load CSV file in Oracle Database using PL/SQL
- Load CSV file in Oracle Database using External Tables
- Create CSV file using PL/SQL
- JSON in Oracle Database with Examples