Oracle SQL*Loader

GeneralNote: This page consists    of a series of demonstrations of various SQL*Loader capabilities. It is by no means      complete.

      For the Oracle doc:
      http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14215/app_ldr_syntax.htm#i631434SQL Loader Data TypesCHAR
    DECIMAL EXTERNAL
INTEGER EXTERNALModesAPPEND
INSERT
REPLACE
TRUNCATEINFILEINFILE * or INFILE '<file_name>'
      [RECSIZE <integer> BUFFERS <integer>]INFILE 'mydata.dat' "RECSIZE 80 BUFFERS 8"INTOINTO <table_name>INTOTABLE empBADFILE

      Records with formatting errors or that cause Oracle errorsBADFILE '<file_name>'BADFILE 'sample.bad'DISCARDFILE

      Records not satisfying a WHEN clauseDISCARDFILE '<file_name>'
      DISCARDMAX <integer>DISCARDFILE 'sample.dsc'CHARACTERSETCHARACTERSET <character_set_name>CHARACTERSET      WE8MSWIN1252LENGTHLENGTH SEMANTICS <BYTE | CHAR>      LENGTH SEMANTICS BYTE
-- this is the default for all character sets except      UTF16LOAD TYPESAPPEND
INSERT
REPLACE
TRUNCATEAPPENDOPTIONS CLAUSEBINDSIZE = n
      COLUMNARRAYROWS = n
      DIRECT = {TRUE | FALSE}
      ERRORS = n
      LOAD = n
      MULTITHREADING = {TRUE | FALSE}
      PARALLEL = {TRUE | FALSE}
      READSIZE = n
      RESUMABLE = {TRUE | FALSE}
      RESUMABLE_NAME = 'text string'
      RESUMABLE_TIMEOUT = n
      ROWS = n
      SILENT = {HEADER | FEEDBACK | ERRORS | DISCARDS | PARTITIONS | ALL}
      SKIP = n
      SKIP_INDEX_MAINTENANCE = {TRUE | FALSE}
      SKIP_UNUSABLE_INDEXES = {TRUE | FALSE}
      STREAMSIZE = nOPTIONS (BINDSIZE=100000, SILENT=(ERRORS, FEEDBACK))PATHSCONVENTIONAL PATH
    DIRECT PATH

      All loads demonstrated below are convention with the exception of demo 6. TERMINATORSComma

','

Tab0x'09'TRAILING NULLCOLSTRAILING NULLCOLS--      assuming this data
      10 Accounting

      -- the following
INTO TABLE dept
    TRAILING NULLCOLS
      ( deptno CHAR TERMINATED BY " ",
  dname  CHAR TERMINATED BY WHITESPACE,
  loc    CHAR TERMINATED BY WHITESPACE)

-- would generate an error without TRAILING NULLCOLS
      -- as it doesn't have loc dataWHENWHEN <condition>See Demo 5 below Assembling Logical RecordsCONCATENATECONCATENATE <number_of_physical_records>CONCATENATE 3CONTINUEIFCONTINUEIF THIS [PRESERVE] (start_position:end_position)      = valueCONTINUEIF THIS (1:2) = '%%'

      CONTINUEIF THIS PRESERVE (1:2) = '%%'CONTINUEIFCONTINUEIF NEXT [PRESERVE] (start_position:end_position)      = valueCONTINUEIF NEXT (1:2) = '%%'

      CONTINUEIF NEXT PRESERVE (1:2) = '%%'CONTINUEIFCONTINUEIF LAST (start_position:end_position)      = value--      Tests against the last non-blank character.
      -- Allows only a single character for the testPRESERVEPreserves the CONTINUEIF characters Demo Tables & Data
      Demo TablesCREATETABLE dept (
      deptno   VARCHAR2(2),
      dname    VARCHAR2(20),
      loc      VARCHAR2(20));

CREATE TABLE emp (
      empno    NUMBER(4),
      ename    VARCHAR2(10),
      job      VARCHAR2(10),
      mgr      NUMBER(4),
      hiredate DATE,
      sal      NUMBER(8,2),
      comm     NUMBER(7,2),
      deptno   NUMBER(2),
      projno   NUMBER(4),
      loadseq  NUMBER(3));

CREATE TABLE proj (
      emp      NUMBER(4),
      projno   NUMBER(3));

CREATE TABLE funcdemo (
      last_name  VARCHAR2(20),
      first_name VARCHAR2(20));

CREATE TABLE decodemo (
      fld1    VARCHAR2(20),
      fld2    VARCHAR2(20));

CREATE TABLE denver_prj (
      projno  VARCHAR2(3),
      empno   NUMBER(5),
      projhrs NUMBER(2));

CREATE TABLE orlando_prj (
      projno  VARCHAR2(3),
      empno   NUMBER(5),
      projhrs NUMBER(2));

CREATE TABLE misc_prj (
      projno  VARCHAR2(3),
      empno   NUMBER(5),
      projhrs NUMBER(2));

CREATE TABLE po_tab  OF XMLTYPE;

CREATE TABLE loadnums(
      col1 VARCHAR2(10),
      col2 NUMBER); Demo 1Basic import of delimited data with data in the control file

Control File

OPTIONS (ERRORS=500, SILENT=(FEEDBACK))
      LOAD DATA
    INFILE *
INTO TABLE <table_name>
FIELDS TERMINATED BY <delimiter>
OPTIONALLY ENCLOSED BY <enclosing character>
    (<column_name>, <column_name>, <column_name>)    sqlldr userid=uwclass/uwclass control=c:\load\demo01.ctl    log=d:\load\demo01.log Demo 2Basic import of fixed length data with separate data and control files

Control File

Data File

LOAD DATA
INFILE <data_file_path_and_name>
INTO TABLE <table_name> (
    <column_name> POSITION(<integer>:<integer>) <data_type>,
    <column_name> POSITION(<integer>:<integer>) <data_type>,
    <column_name> POSITION(<integer>:<integer>) <data_type>)sqlldr userid=uwclass/uwclass      control=c:\load\demo02.ctl log=c:\load\demo02.log Demo 3Append of delimited data with data in the      control file. This sample demonstrates date formating, delimiters within delimiters and      implementation of record numbering with a SQL*Loader sequence. APPEND indicates that      the table need not be empty before the SQL*Loader is run.

Control File

LOAD DATA
    INFILE *
APPEND
INTO TABLE emp
    FIELDS TERMINATED BY ","
    OPTIONALLY ENCLOSED BY '"'
    (<column_name>, <column_name> DATE "DD-Month-YYYY",
    <column_name> CHAR TERMINATED BY ':',
    <column_name> SEQUENCE(MAX,1))    sqlldr userid=uwclass/uwclass control=c:\load\demo03.ctl log=c:\load\demo3.log Demo 4Replace of fixed length data with separate data and control file. This sample     demonstrates specifying a discard file, the maximum number of records to discard (DISCARDMAX), and CONTINUEIF (    where it looks for an asterisk in the first position to determine if a new     line has started.Control File

Data FileLOAD DATA
    INFILE 'c: emp\demo04.dat'
DISCARDFILE 'c: emp\demo4.dsc'
      DISCARDMAX 999
REPLACE
      CONTINUEIF THIS (1) = '*'
INTO TABLE emp (
    empno    POSITION(1:4)   INTEGER EXTERNAL,
    ename    POSITION(6:15)  CHAR,
    hiredate POSITION(52:60) INTEGER EXTERNAL)    sqlldr userid=uwclass/uwclass control=c:\load\demo04.ctl log=c:\load\demo4.log Demo 5Loading into multiple tables during an import using the WHEN keyword. The       control file loads two different tables making three passes at one      of them. Note the problem with the Doolittle record and how it is handled.

Control File

Data File

LOAD DATA
      INFILE 'c: emp\demo05.dat'
BADFILE 'c: emp\bad05.bad'
      DISCARDFILE 'c: emp\disc05.dsc'
REPLACE

INTO TABLE emp (
      empno  POSITION(1:4)   INTEGER EXTERNAL,
      ename  POSITION(6:15)  CHAR,
      deptno POSITION(17:18) CHAR,
      mgr    POSITION(20:23) INTEGER EXTERNAL)

      --1st project: proj has two columns, both not null
INTO TABLE proj
WHEN projno != '   ' (
      emp    POSITION(1:4)   INTEGER EXTERNAL,
      projno POSITION(25:27) INTEGER EXTERNAL)

      -- 2nd project
INTO TABLE proj
WHEN projno != '   ' (
      emp    POSITION(1:4)   INTEGER EXTERNAL,
      projno POSITION(29:31) INTEGER EXTERNAL)

      -- 3rd project
INTO TABLE proj
WHEN projno != '   ' (
      emp    POSITION(1:4)   INTEGER EXTERNAL,
      projno POSITION(33:35) INTEGER EXTERNAL)    sqlldr userid=uwclass/uwclass control=c:\load\demo5.ctl log=d:\load\demo5.log Demo 6Using the NULLIF and BLANKS keywords to handle zero length strings being loaded     into numeric columns. Also note the use of Direct Path Load in the control file      (DIRECT=TRUE).

Control File

Data File

LOAD DATA
      INFILE 'c: emp\demo06.dat'
INSERT
INTO TABLE emp
      -- SORTED INDEXES (emp_empno)
      (
      empno  POSITION(01:04) INTEGER EXTERNALNULLIF      empno=BLANKS,
      ename  POSITION(06:15) CHAR,
      job    POSITION(17:25) CHAR,
      mgr    POSITION(27:30) INTEGER EXTERNALNULLIF      mgr=BLANKS,
      sal    POSITION(32:39) DECIMAL EXTERNAL NULLIF      sal=BLANKS,
      comm   POSITION(41:48) DECIMAL EXTERNAL NULLIF      comm=BLANKS,
      deptno POSITION(50:51) INTEGER EXTERNALNULLIF      deptno=BLANKS)    sqlldr userid=uwclass/uwclass control=c:\load\demo06.ctl    log=c:\load\demo06.logDIRECT=TRUE Demo 7Using a buit-in function to modify data during loading

Control File

LOAD DATA
      INFILE *
INSERT
INTO TABLE funcdemo
      (
      LAST_NAME  position(1:7)  CHAR "UPPER(:LAST_NAME)",
      FIRST_NAME position(8:15) CHAR "LOWER(:FIRST_NAME)"
      )
      BEGINDATA
      Locke  Phil
      Gorman Tim    sqlldr userid=uwclass/uwclass control=c:\load\demo07.ctl    log=c:\load\demo07.log Demo 8Another example of using a      built-in function, in this case DECODE, to modify data during loading

Control File

LOAD DATA
      INFILE *
INSERT
INTO TABLE decodemo
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY '"'
      (
      fld1,
      fld2 "DECODE(:fld1, 'hello', 'goodbye', :fld1)"
      )
      BEGINDATA
      hello,""
      goodbye,""
      this is a test,""
      hello,""    sqlldr userid=uwclass/uwclass control=c:\load\demo08.ctl    log=c:\load\demo08.log Demo 9    Loading multiple files into multiple tables in a singe control file. Note the use of the WHEN keyword.

Control File

Data File

Data File

LOAD DATA
INFILE 'c: emp\demo09a.dat'
      INFILE 'c: emp\demo09b.dat'
APPEND
INTO TABLE denver_prj
WHEN projno = '101' (
      projno  position(1:3)  CHAR,
      empno   position(4:8)  INTEGER EXTERNAL,
      projhrs position(9:10) INTEGER EXTERNAL)

INTO TABLE orlando_prj
WHEN projno = '202' (
      projno  position(1:3)  CHAR,
      empno   position(4:8)  INTEGER EXTERNAL,
      projhrs position(9:10) INTEGER EXTERNAL)

INTO TABLE misc_prj
WHEN projno != '101'AND projno != '202' (
      projno  position(1:3)  CHAR,
      empno   position(4:8)  INTEGER EXTERNAL,
      projhrs position(9:10) INTEGER EXTERNAL)    sqlldr userid=uwclass/uwclass control=c:\load\demo09.ctl    log=c:\load\demo09.log Demo 10    Loading negative numeric values. Note Clark and Miller's records in the data file.    Note empty row

Control File

Data File

LOAD DATA
      INFILE 'c: emp\demo10.dat'
INTO TABLE emp
      REJECT ROWS WITH ALL NULL FIELDS
      (
      empno  POSITION(01:04) INTEGER EXTERNAL,
      ename  POSITION(06:15) CHAR,
      job    POSITION(17:25) CHAR,
      mgr    POSITION(27:30) INTEGER EXTERNAL,
      sal    POSITION(32:39) DECIMAL EXTERNAL,
      comm   POSITION(41:48) DECIMAL EXTERNAL,
      deptno POSITION(50:51) INTEGER EXTERNAL)    sqlldr userid=uwclass/uwclass control=c:\load\demo10.ctl log=c:\load\demo10.log Demo 11Loading XML

      Control    File

LOAD DATA
      INFILE *
INTO TABLE po_tab
      APPEND
      XMLTYPE (xmldata)
      FIELDS
      (xmldata CHAR(2000))      desc po_tab

      sqlldr userid=uwclass/uwclass control=c:\load\demo11.ctl log=c:\load\demo11.log

      set long 1000000

SELECT * FROM po_tab;

SELECT *
FROM po_tab
WHERE sys_nc_rowinfo$  LIKE '%Hurry%'; Demo 12Loading a CONSTANT, RECNUM, and     SYSDATE

      Control    File

OPTIONS (ERRORS=100, SILENT=(FEEDBACK))
      LOAD DATA
      INFILE *
REPLACE
INTO TABLE dept
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY '"'
      (recno RECNUM, deptno CONSTANT "XX", dname, loc, tdate      SYSDATE)ALTERTABLE dept
    ADD (recno NUMBER(5), tdate DATE);

    desc dept

    sqlldr userid=uwclass/uwclass control=c:\load\demo12.ctl    log=c:\load\demo12.log Demo 13Setting READSIZE and BINDSIZE

      The control file and data for
      this demo can be found in
      /demo/schema/sales_history/
      schema under $ORACLE_HOME
      as cust1v3.ctl and cust1v3.dat

      BINDSIZE and READSIZE
      do not apply to Direct Path Loads

LOAD DATA
      INFILE 'c: emp\cust1v3.dat'
INTO TABLE CUSTOMERS
TRUNCATE
      FIELDS TERMINATED BY '|'
      OPTIONALLY ENCLOSED BY '"'
      (CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER, CUST_YEAR_OF_BIRTH,      CUST_MARITAL_STATUS, CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY,      CUST_CITY_ID, CUST_STATE_PROVINCE, CUST_STATE_PROVINCE_ID, COUNTRY_ID,      CUST_MAIN_PHONE_NUMBER, CUST_INCOME_LEVEL, CUST_CREDIT_LIMIT, CUST_EMAIL, CUST_TOTAL, CUST_TOTAL_ID, CUST_SRC_ID, CUST_EFF_FROMDATE(19) "YYYY-MM-DD-HH24-MI-SS",      CUST_EFF_TODATE(19) "YYYY-MM-DD-HH24-MI-SS", CUST_VALID)conn sh/sh

      GRANT select ON customers TO uwclass;

      conn uwclass/uwclass

CREATE TABLE customers AS
SELECT * FROM sh.customers
WHERE 1=2;

      desc customers

-- run 1 - default sizing
    sqlldr userid=uwclass/uwclass control=c:\load\demo13.ctl      log=c:\load\demo13.log

      Space allocated for bind array:                 251252 bytes(46 rows)
      Read   buffer bytes: 1048576
      Elapsed time was:     00:00:03.73
      CPU time was: 00:00:01.25

-- run 2 - double default to 2M
    sqlldr userid=uwclass/uwclass control=c:\load\demo13.ctl      log=c:\load\demo13.logreadsize=2048000 bindsize=2048000 rows=64

      Space allocated for bind array:                 349568 bytes(64 rows)
      Read   buffer bytes: 2048000

      Elapsed time was:     00:00:03.50
      CPU time was: 00:00:01.09

-- run 3 - double default to 4M
    sqlldr userid=uwclass/uwclass control=c:\load\demo13.ctl
      log=c:\load\demo13.log readsize=4096000 bindsize=4096000 rows=64

      Space allocated for bind array:                 349568 bytes(64 rows)
      Read   buffer bytes: 4096000

      Elapsed time was:     00:00:03.65
      CPU time was: 00:00:01.12 Demo 14Trailing

      Load Numbers with trailing + and - signs

LOAD DATA
      INFILE *
TRUNCATE
INTO TABLE loadnums (
col1 position(1:5),
      col2 position(7:16) "TO_NUMBER(:col2,'99,999.99MI')")
      BEGINDATA
      abcde 1,234.99-
      abcde 11,234.34+
      abcde 45.23-
      abcde 99,234.38-
      abcde 23,234.23+
      abcde 98,234.23+    sqlldr userid=uwclass/uwclass control=c:\load\demo14.ctl    log=c:\load\demo09.log