SQL*Loader

 

Oracle SQL*Loader

Version 10.2

General

Note: 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#i631434

SQL Loader Data Types

CHAR
DECIMAL EXTERNAL
INTEGER EXTERNAL

Modes

APPEND
INSERT
REPLACE
TRUNCATE

INFILE

INFILE * or INFILE '<file_name>'
[RECSIZE <integer> BUFFERS <integer>]

INFILE 'mydata.dat' "RECSIZE 80 BUFFERS 8"

INTO

INTO <table_name>

INTO TABLE emp

BADFILE

Records with formatting errors or that cause Oracle errors

BADFILE '<file_name>'

BADFILE 'sample.bad'

DISCARDFILE

Records not satisfying a WHEN clause

DISCARDFILE '<file_name>'
DISCARDMAX <integer>

DISCARDFILE 'sample.dsc'

CHARACTERSET

CHARACTERSET <character_set_name>

CHARACTERSET WE8MSWIN1252

LENGTH

LENGTH SEMANTICS <BYTE | CHAR>

LENGTH SEMANTICS BYTE
-- this is the default for all character sets except UTF16

LOAD TYPES

APPEND
INSERT
REPLACE
TRUNCATE

APPEND

OPTIONS CLAUSE

BINDSIZE = 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 = n

OPTIONS (BINDSIZE=100000, SILENT=(ERRORS, FEEDBACK))

PATHS

CONVENTIONAL PATH
DIRECT PATH

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

TERMINATORS

Comma	','
Tab	0x'09'

TRAILING NULLCOLS

TRAILING 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 data

WHEN

WHEN <condition>

See Demo 5 below

Assembling Logical Records

CONCATENATE

CONCATENATE <number_of_physical_records>

CONCATENATE 3

CONTINUEIF

CONTINUEIF THIS [PRESERVE] (start_position:end_position) = value

CONTINUEIF THIS (1:2) = '%%'

CONTINUEIF THIS PRESERVE (1:2) = '%%'

CONTINUEIF

CONTINUEIF NEXT [PRESERVE] (start_position:end_position) = value

CONTINUEIF NEXT (1:2) = '%%'

CONTINUEIF NEXT PRESERVE (1:2) = '%%'

CONTINUEIF

CONTINUEIF LAST (start_position:end_position) = value

-- Tests against the last non-blank character.
-- Allows only a single character for the test

PRESERVE

Preserves the CONTINUEIF characters

Demo Tables & Data

Demo Tables

CREATE TABLE 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 1

Basic 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 2

Basic 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 3

Append 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 4

Replace 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 File

LOAD 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 5

Loading 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 6

Using 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 EXTERNAL NULLIF empno=BLANKS,
ename  POSITION(06:15) CHAR,
job    POSITION(17:25) CHAR,
mgr    POSITION(27:30) INTEGER EXTERNAL NULLIF 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 EXTERNAL NULLIF deptno=BLANKS)

sqlldr userid=uwclass/uwclass control=c:\load\demo06.ctl log=c:\load\demo06.log DIRECT=TRUE

Demo 7

Using 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 8

Another 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 11

Loading 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 12

Loading 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)

ALTER TABLE dept
ADD (recno NUMBER(5), tdate DATE);

desc dept

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

Demo 13

Setting READSIZE and BINDSIZE

The control file and data for 
this demo can be found in 
/demo/schema/sales_history/
schema under
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_FROM DATE(19) "YYYY-MM-DD-HH24-MI-SS", CUST_EFF_TO DATE(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.log readsize=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 14

Trailing

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