| General |
| Note: This page consists of a series of demonstrations of various SQL*Loader capabilities. It is by no means complete.
|
| 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> |
| B |
| 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> |
| X |
| LENGTH | LENGTH [SEMANTICS] <BYTE | CHAR> |
| CHARACTERSET <character_set_name>
LENGTH |
| Load Type | APPEND
INSERT
REPLACE |
| INSERT INTO <table_name> |
| Terminators | |
| WHEN | WHEN |
| I |
| TRAILING NULLCOLS | TRAILING NULLCOLS |
| T |
| 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. |
|
|
|
|
| Assembling Logical Records |
| CONCATENATE | CONCATENATE |
|
|
| CONTINUEIF | CONTINUEIF THIS |
|
|
| CONTINUEIF | CONTINUEIF NEXT |
|
|
| CONTINUEIF | CONTINUEIF LAST |
|
|
|
|
| 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)); |
|
|
| Demo 1 |
| Basic import of delimited data with data in the control file |
| Control File | LOAD DATA
INFILE *
INTO TABLE <table_name>
FIELDS TERMINATED BY <delimiter>
OPTIONALLY ENCLOSED BY <enclosing character>
(<column_name>, <column_name>, <column_name>) |
| sqlldr userid=dev/dev control=c:\load\demo1.ctl log=d:\load\sqlldr01.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=dev/dev control=c:\load\sqlldr02.ctl log=c:\load\demo02.log |
|
|
| Demo 3 |
| Append of delimited data with data in the control file. This sample demonstrates date formatting, 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=dev/dev control=c:\load\sqldr03.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\sqlldr04.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=dev/dev control=c:\load\sqlldr04.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\sqlldr05.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=dev/dev control=c:\load\sqlldr5.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\sqlldr06.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=dev/dev control=c:\load\sqlldr06.ctl log=c:\load\demo6.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=dev/dev control=c:\load\sqlldr7.ctl log=c:\load\demo7.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=dev/dev control=c:\load\sqlldr08.ctl log=c:\load\demo8.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\sqlldr9a.dat'
INFILE 'c:\temp\sqlldr9b.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=dev/dev control=c:\load\sqlldr09.ctl log=c:\load\demo9.log |
|
|
| Demo 10 |
| Loading negative numeric values. Note Clark and Miller's records in the data file. |
| Control File
Data File | LOAD DATA
INFILE 'c:\temp\sqlldr10.dat'
INTO TABLE emp
(
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=dev/dev control=c:\load\sqlldr10.ctl log=c:\load\demo10.log |
|
|
| Demo 11 |
| Loading XML |
| Control File
Data File | LOAD DATA
INFILE *
INTO TABLE po_tab
APPEND
XMLTYPE (xmldata)
FIELDS
(xmldata CHAR(2000))
BEGINDATA
<?xml version="1.0"?>
<purchaseOrder xmlns="http://www.oracle.com/PO" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.oracle.com/PO http://www.oracle.com/scha0/po1.xsd"
orderDate="1999-10-20">
<shipTo country="US">
<name>Alice Smith</name>
<street>123 Maple Street</street>
<city>Mill Valley</city>
<state>CA</state>
<zip>90952</zip>
</shipTo>
<billTo country="US">
<name>Robert Smith</name>
<street>8 Oak Avenue</street>
<city>Old Town</city>
<state>PA</state>
<zip>95819</zip>
</billTo>
<comment>Hurry, my lawn is going wild!</comment>
<items>
<item partNum="872-AA">
<productName>Lawnmower</productName>
<quantity>1</quantity>
<USPrice>148.95</USPrice>
<comment>Confirm this is electric</comment>
</item>
<item partNum="926-AA">
<productName>Baby Monitor</productName>
<quantity>1</quantity>
<USPrice>39.98</USPrice>
<shipDate>1999-05-21</shipDate>
</item>
</items>
</purchaseOrder> |
| sqlldr userid=dev/dev control=c:\load\sqlldr11.ctl log=c:\load\demo11.log |
