Oracle SQL*Loader
Version 12.2

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Note SQL*Loader is a legacy tool focused on loading physical files, primarily fixed length and delimited into database tables. This page consists of a series of demonstrations of various SQL*Loader capabilities. It is by no means complete so you need to read the docs. It is also important to note that SQL*Loader has become, for all intents and purposes, a dinosaur tool and it is recommended that almost all activities be performed using External Tables (linked at page bottom).
 
Common Syntax Elements
SQL Loader Data Types
CHAR DECIMAL EXTERNAL INTEGER EXTERNAL
Modes (or) Load Types
APPEND REPLACE TRUNCATE
INSERT    
BADFILE

Records with formatting errors or that cause Oracle errors		 BADFILE '<file_name>'
BADFILE 'sample.bad'
CHARACTERSET CHARACTERSET <character_set_name>
CHARACTERSET WE8MSWIN1252
COLUMNARRAYROWS

The number of rows to allocate for direct path column arrays		 COLUMNARRAYROWS <integer>
COLUMNARRAYROWS 200
CONTROL

The name of the control file holding the parameters to be run		 CONTROL '<file_name>'
See Demos Below
DATE_CACHE

Specifies the date cache size (in entries, default 1000)		 DATE_CACHE = <integer>
DATE_CACHE = 0
DIRECT

Specifies use of a direct path load (default FALSE)		 DIRECT
See Demos 6 Below
DISCARDFILE and DISCARDMAX

Records not satisfying a WHEN clause		 DISCARDFILE '<file_name>'
DISCARDMAX <integer>
See Demo 4 Below
ERRORS

The maximum number of insert errors to allow		 ERRORS = <integer>
ERRORS = 5
FILE

The database file from which to allocate new extents		 FILE=<file_path_and_name_string>
FILE = C:\APP\ORACLE\PRODUCT\ORADATA\ORABASE\PDBDEV\UWDATA02.DBF
INFILE INFILE * or INFILE '<file_name>'
[RECSIZE <integer> BUFFERS <integer>]
INFILE 'mydata.dat' "RECSIZE 80 BUFFERS 8"
INTO INTO <table_name>
INTO TABLE emp
LENGTH LENGTH SEMANTICS <BYTE | CHAR>
LENGTH SEMANTICS BYTE
-- this is the default for all character sets except UTF16
LOAD

The maximum number of logical records to load		 LOAD = <number of records to load>
LOAD = 10000
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
See Demo 6 below
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 THIS CONTINUEIF THIS [PRESERVE] (start_position:end_position) = value
CONTINUEIF THIS (1:2) = '%%'

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

CONTINUEIF NEXT PRESERVE (1:2) = '%%'
CONTINUEIF LAST 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@pdbdev 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@pdbdev 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@pdbdev 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:\temp\demo04.dat'
DISCARDFILE 'c:\temp\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@pdbdev 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 isse with the Doolittle record and how it is handled.
Control File

Data File		 LOAD DATA
INFILE 'c:\temp\demo05.dat'
BADFILE 'c:\temp\bad05.bad'
DISCARDFILE 'c:\temp\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@pdbdev 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:\temp\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@pdbdev 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
Kurian Thomas
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@pdbdev 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:\temp\demo09a.dat'
INFILE 'c:\temp\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@pdbdev 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:\temp\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@pdbdev 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@pdbdev 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@pdbdev 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 $ORACLE_HOME as cust1v3.ctl and cust1v3.dat

BINDSIZE and READSIZE do not apply to Direct Path Loads		 LOAD DATA
INFILE 'c:\temp\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@pdbdev

GRANT select ON customers TO uwclass;

conn uwclass/uwclass@pdbdev

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

desc customers

-- run 1 - default sizing
sqlldr userid=uwclass/uwclass@pdbdev 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@pdbdev 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@pdbdev 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
Sign Trailing
Load Numbers with trailing + and - signs

Be sure to also look up the keyword ZONED in the Oracle docs for another way to handle this type of data		 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/pdbdev control=c:\load\demo14.ctl log=c:\load\demo14.log
 
Demo 15
Filler
Ignoring information that should not be loaded LOAD DATA
INFILE *
INTO TABLE emp
APPEND
FIELDS TERMINATED BY ','
(
EMPNO,
ENAME,
JOB,
JUNK1 FILLER,
JUNK2 FILLER,
SAL)
BEGINDATA
98,MORGAN,SPEAKER,badinfo,ignoreme,1.00,0
99,ELLISON,CEO,badinfo,ignoreme,99999.00,0
sqlldr userid=scott/tiger@pdbdev control=c:\load\demo15.ctl log=c:\load\demo15.log
 
Demo 16
External Table Generation
SQL*Loader used to generate external table DDL which can be found in the log file

Control File

Generated Log File		 LOAD DATA
APPEND
INTO TABLE dept
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(DEPTNO, DNAME, LOC)
sqlldr userid=scott/tiger@pdbdev control=c:\load\demo16.ctl log=c:\load\demo16.log external_table=generate_only

Related Topics
Data Pump
DBMS_LOB
Export
External Tables
Import
MultiMedia Audio
MultiMedia Video
UTL_FILE
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx