| 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 |
|
| 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:\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 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:\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 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 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
Cline Jack |
| 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:\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 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 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 $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
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 |