본문 바로가기

전산Tip/Oracle

Oracle 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:\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
 
Related Topics
Data Pump
DBMS_LOB
External Tables
Import
InterMedia Audio
InterMedia Video
UTL_FILE

'전산Tip > Oracle' 카테고리의 다른 글

대용량 Sam 파일 오라클에 Load하기  (1) 2008.09.21
Oracle External Tables  (4) 2008.09.18
PL/SQL에서 DBMS_PIPE를 이용해서 PRO*C CALL하기  (0) 2008.09.18