/*
SQLLDR 테스트
*/
/*
Case #1 : 가변길이 자료
가장 기본적인 Case 이다. 콘트롤 파일에 로딩할 자료를 포함하고 있다.
자료는 길이가 고정되어 있지 않고 가변적이다.
필드는 기본적으로 콤마(,)로 구분되고, 필요에 따라서 이중따옴표(")로 묶여 있다.
*/
-- 테이블 구조
CREATE TABLE DEPT_NOM(
DEPTNO NUMBER(2),
DNAME CHAR(14),
LOC CHAR(13)
);
-- 컨트롤 파일 ( ulcase1.ctl )
LOAD DATA
INFILE *
INTO TABLE DEPT_NOM
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ""
(DEPTNO, DNAME, LOC)
BEGINDATA
12,RESEARCH, "SARATOGA"
10,"ACCOUNTING",CLEVELAND
11,"ART",SALEM
13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT'L","SAN FRAN"
-- 실행
D:\>sqlldr userid=hr/hr control=ulcase1.ctl log=ulcase1.log
그림 001
그림 002 - INSERT 시 해당 테이블에 데이터가 있으면 안된다.
-- TRUNCATE TABLE DEPT_NOM;
SELECT * FROM DEPT_NOM;
/*
Case #2 : 고정포맷 자료
로딩할 자료가 고정된 포맷(모든 레코드의 각 필드가 같은 Byte 위치에서 시작)인 경우에 사용한다.
필드가 시작하는 위치를 정확히 계산해야 한다. 특히, Tab문자는 1 Byte로 계산해야 한다.
또한, 한 Line에 여러 레코드가 있어서는 안된다.
*/
-- 테이블
CREATE TABLE EMP_NOM(
empno number(4) not null,
ename char(10),
job char(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2)
)
-- 컨트롤 파일 ( ulcase2.ctl )
LOAD DATA
INFILE 'ulcase2.dat'
INTO TABLE EMP_NOM
(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)INTERGER EXTERNAL)
-- 로딩할 DATA FILE 지정
-- 데이타를 Byte 단위로 정확히 잘라서 로딩할 때, POSITION(from:to) 키워드를 사용하며,
-- (from:to)는 DATA FILE에서 해당 필드의 Byte (시작:종료) 위치를 가리킨다.
-- 데이터 파일 ( ulcase2.dat )
7782 CLARK MANAGER 7839 2572.50 10
7839 KING PRESIDENT 5500.00 10
7934 MILLER CLERK 7782 920.00 10
7566 JONES MANAGER 7839 3123.75 20
7499 ALLEN SALESMAN 7698 1600.00 300.00 30
7654 MARTIN SALESMAN 7698 1312.50 1400.00 30
7658 CHAN ANALYST 7566 3450.00 20
-- 로딩할 자료가 공백인 경우, 자동으로 NULL이 입력된다.
--※ EXTERNAL 키워드는 숫자형 데이타 타입(INTEGER, FLOAT, DECIMAL, ZONED)에 따라붙는데,
-- 이는 문자(Character)로 표시된 숫자를 읽을 때 사용한다.
-- 반대로 숫자가 binary 형태인 경우는 INTEGER, DECIMAL 등을 사용한다.
-- 실행
D:\>sqlldr userid=hr/hr control=ulcase2.ctl log=ulcase2.log
그림 003
-- TRUNCATE TABLE EMP_NOM;
SELECT * FROM EMP_NOM;
/*
Case #3 : 구분자, Date, Sequence 사용
다음은 특정 구분자(':')로 구분된 경우와 Date Tyype의 처리, SEQUENCE 함수의 사용 예이다.
*/
-- 테이블
CREATE TABLE EMP_NOM2(
EMPNO NUMBER(4) NOT NULL,
ENAME CHAR(10),
JOB CHAR(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2),
PROJNO NUMBER,
LOADSEQ NUMBER
);
-- 컨트롤 파일 ( ulcase3.ctl )
LOAD DATA
INFILE *
APPEND
INTO TABLE EMP_NOM2
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE DATE(20) "DD-Month-YYYY",
SAL,
COMM,
DEPTNO CHAR TERMINATED BY ":",
PROJNO,
LOADSEQ SEQUENCE(MAX, 1)
)
BEGINDATA
7782,"Clark","Manager",7839,09-4월-1981,2572.50,,10:101
7839,"King","President",,17-11월-1981,5500.00,,10:102
7934,"Miller","Clerk",7782,23-1월-1982,920.00,,10:102
7566,"Jones","Manager",7839,02-3월-1981, 3123.75,,20:101
7499,"Allen","Salesman",7698,20-2월-1981,1600.00,300.00,30:103
7654,"Martin","Salesman",7698,28-12월-1981,1312.50,1400.00,30:103
7658,"Chan","Analyst",7566,03-5월-1982,3450,,20:101
-- 날짜 TYPE을 시스템 날짜에 맞추어야 입력 가능하다.
SELECT SYSDATE FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'DD-Month-YYYY') "TODAY" FROM DUAL;
/*
- DATA Type을 지정하지 않은 곳은 Default로 char(255)가 된다.
- : 문자를 만날 때까지의 문자열 필드
- loadseq 필드의 최대값을 구한 후, 1씩 증가시킨 값을 데이타로 사용한다.
*/
-- 실행
D:\>sqlldr userid=hr/hr control=ulcase3.ctl log=ulcase3.log
-- TRUNCATE TABLE EMP_NOM2;
SELECT * FROM EMP_NOM2;
/*
※ 데이타 없이 SQL*Loader 자체적으로 로딩할 자료를 만들수 있는 지시어
CONSTANT : 상수를 지정하여 특정 컬럼에 지정된 상수를 로딩한다.
RECNUM : 로딩되는 레코드의 현재 Count를 특정 컬럼에 로딩한다.
SYSDATE : 특정 컬럼에 현재 날짜를 로딩한다.
SEQUENCE(start,inc) : start부터 inc만큼씩 증가하는 수를 로딩한다.
- start에는 MAX, COUNT와 같은 Keyword를 사용할 수 있다.
- MAX는 현재 컬럼에 존재하는 값들 중 가장 큰 값에 inc를 더한 값이다.
- COUNT는 로딩할 테이블에 이미 존재하는 레코드건수에 inc를 더한 값이다.
*/
/*
Case #4 : 한 레코드가 여러줄로 구성
하나의 레코드가 데이타파일에서 여러줄로 구성되어 있는 경우 continueif 문을 사용하는 예이다.
*/
-- 테이블
CREATE TABLE EMP_NOM3(
EMPNO NUMBER(4) NOT NULL,
ENAME CHAR(10),
JOB CHAR(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);
CREATE UNIQUE INDEX EMPIX ON EMP_NOM3(EMPNO);
-- 콘트롤 파일( ulcase4.ctl )
LOAD DATA
INFILE "ulcase4.dat"
DISCARDFILE "ulcase4.dis"
DISCARDMAX 999
REPLACE
CONTINUEIF(1)='*'
INTO TABLE EMP_NOM3
(
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,
HIREDATE POSITION(52:60)INTEGER EXTERNAL
)
/*
- Discard 파일을 ulcase4.dis 로 생성한다.
Discard 되는 레코드가 999 건 까지는 허용하고, 그보다 많으면, 로딩을 중단한다.
단, 이번 예제에는 WHEN 조건이 없어서 Discard 파일은 실제로 생성되지 않는다.
- Replace 옵션은 로딩할 테이블의 기존 데이타를 모두 삭제하고 로딩한다.
- 라인의 첫번째 컬럼에 '*' 문자가 있으면, 다음에 나오는 Line을 현재 Line의 연장으로 간주한다.
*/
-- 데이터 파일 ( ulcase4.dat )
*7782 CLARK MA
NAGER 7839 2572.50 -10 2585/11/12
*7839 KING PR
ESIDENT 5500.00 2583/10/03
*7934 MILLER CL
ERK 7782 920.00 2580/05/03
*7934 MILLER CL
ERK 7782 920.00 2580/05/03
* MILLER CL
ERK 7782 920.00 2580/05/03
-- 실행
D:\>sqlldr userid=hr/hr control=ulcase4.ctl log=ulcase4.log
-- TRUNCATE TABLE EMP_NOM3;
SELECT * FROM EMP_NOM3;
-- EMPNO가 없어 ORA-01400 에러가 발생하고, 해당 레코드는 BAD File에 기록된다.
-- EMPNO 7658 번이 앞에서 이미 등록되어, ORA-00001 에러가 발생하고, 해당 레코드는 BAD File에 기록된다.
-- ulcase4.log
레코드 4: 기각됨 - 테이블 EMP_NOM3에 오류
ORA-00001: 무결성 제약 조건(HR.EMPIX)에 위배됩니다
-- ulcase4.bad
*7934 MILLER CL
ERK 7782 920.00 2580/05/03
/*
Case #5 : 동시에 여러 테이블로 로딩
데이타 파일에서 한 레코드를 읽어서 여러 테이블에 동시에 로딩하는 예이다.
*/
-- 테이블 구조
CREATE TABLE EMP_NOM4 (
EMPNO NUMBER(4) NOT NULL,
ENAME CHAR(10),
JOB CHAR(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);
CREATE UNIQUE INDEX EMPIX_4 ON EMP_NOM4(EMPNO);
CREATE TABLE PROJ_NOM4 (
EMPNO NUMBER,
PROJNO NUMBER
);
-- 콘트롤 파일(ulcase5.ctl)
LOAD DATA
INFILE 'ulcase5.dat'
BADFILE 'ulcase5.bad'
DISCARDFILE 'ulcase5.dis'
REPLACE
INTO TABLE EMP_NOM4
(
EMPNO POSITION(1:4) INTEGER EXTERNAL,
ENAME POSITION(6:15) CHAR,
DEPTNO POSITION(17:18) CHAR,
MGR POSITION(20:23) INTEGER EXTERNAL
)
-- 1st PROJ
INTO TABLE PROJ_NOM4
WHEN PROJNO != ' '
(
EMPNO POSITION(1:4) INTEGER EXTERNAL,
PROJNO POSITION(25:27) INTEGER EXTERNAL
)
-- 2st PROJ
INTO TABLE PROJ_NOM4
WHEN PROJNO != ' '
(
EMPNO POSITION(1:4) INTEGER EXTERNAL,
PROJNO POSITION(29:31) INTEGER EXTERNAL
)
-- 3st PROJ
INTO TABLE PROJ_NOM4
WHEN PROJNO != ' '
(
EMPNO POSITION(1:4) INTEGER EXTERNAL,
PROJNO POSITION(33:35) INTEGER EXTERNAL
)
/*
- Discard 파일을 ulcase5.dis 로 생성한다. WHEN 조건에 위배되는 레코드가 Discard
파일에 기록된다.
- Replace 옵션으로 인해 로딩할 테이블의 기존 데이타를 모두 삭제하고 로딩한다.
- 여러 필드에서 PROJNO를 구해서 PROJ 테이블에 등록한다.
*/
-- 데이터 파일( ulcase5.dat )
1234 BAKER 10 9999 101 102 103
1234 JOKER 10 9999 777 888 999
2664 YOUNG 20 2893 425 abc 102
5321 OTOOLE 10 9999 321 55 40
2134 FARMER 20 4555 236 456
6542 LEE 10 4532 102 321 14
2849 EDDS xx 4555 294 40
4532 PERKINS 10 9999 40
1244 HUNT 11 3452 665 133 456
123 DOOLITTLE 12 9940 132
1453 MACDONALD 25 5532 200
-- 편의상 칸수 맞추느라 데이터를 표시했을 뿐이지 정확한 바이트 순으로 기록되어 있어야 한다. POSITION(start:end) 주의
-- PROJNO가 없는 경우는 WHEN 조건에 위배되므로, PROJ 테이블에 등록되지 않고,
-- 레코드가 Discard 파일에 기록된다.
-- 실행
D:\>sqlldr userid=hr/hr control=ulcase5.ctl log=ulcase5.log
-- TRUNCATE TABLE EMP_NOM4;
SELECT * FROM EMP_NOM4;
SELECT * FROM PROJ_NOM4;
/*
Case #6 : Direct Path Load
Direct Path Load방식에 의해 로딩하는 예이다.
*/
-- 테이블 구조
CREATE TABLE EMP_NOM6 (
EMPNO NUMBER(4) NOT NULL,
ENAME CHAR(10),
JOB CHAR(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);
CREATE UNIQUE INDEX EMPIX_6 ON EMP_NOM6(EMPNO);
-- 콘트롤 파일(ulcase6.ctl)
LOAD DATA
INFILE 'ulcase6.dat'
REPLACE
INTO TABLE EMP
SORTED INDEXES (EMPIX_6)
(
EMPNO POSITION(1:4),
ENAME POSITION(6:15),
JOB POSITION(17:25),
MGR POSITION(27:30) NULLIF MGR=BLANKS,
SAL POSITION(32:39) NULLIF SAL=BLANKS,
COMM POSITION(41:48) NULLIF COMM=BLANKS,
DEPTNO POSITION(50:51) NULLIF EMPNO=BLANKS
)
/*
- Index를 만들기 위해 소팅하는 시간을 줄일 수 있는 기능이다.
Direct Path Load 시에는 SQL*Loader가 로딩 전에 인덱스를 Drop하고
로딩 후 다시 만드는데, 이때 로딩될 데이타가 이미 소팅되어 있어 empix 인덱스를 만들기 위해 소팅할 필요가 없음을 SQL*Loader에게 알려준다.
- nullif field_name=blanks 필드 위치에 입력할 데이타가 없는 경우, 대신 null 을 입력한다.
- NVL, upper, to_number 등과 같은 함수를 Direct path load에서는 사용할 수 없다.
왜냐하면 SQL Interface를 경유하지 않고 바로 Data block에 기록하기 때문이다.
*/
-- 데이터파일 ( ulcase6.dat )
7499 ALLEN SALESMAN 7698 1600.00 300.00 30
7566 JONES MANAGER 7839 3123.75 20
7654 MARTIN SALESMAN 7698 1312.50 1400.00 30
7658 CHAN ANALYST 7566 3450.00 20
7782 CLARK MANAGER 7839 2572.50 10
7839 KING PRESIDENT 5500.00 10
7934 MILLER CLERK 7782 920.00 10
-- 실행
D:\>sqlldr userid=hr/hr control=ulcase6.ctl log=ulcase6.log
-- TRUNCATE TABLE EMP_NOM6;
SELECT * FROM EMP_NOM6;
/*
※ Conventional Path Load
SQL*Loader 수행 시 direct 옵션을 주지 않는 일반적인 로딩이 여기에 해당된다.
데이타의 로딩을 SQL*Plus 상에서 Insert 문을 수행하듯이 처리한다.
※ Direct Path Load
이전 Block들을 재활용하지 않고, 새로운 Block만 사용하므로, 재활용 가능한 Block들을
검색하는 시간을 줄일 수 있고, Writing 횟수를 줄일 수 있다.
Bind-Array Buffer를 사용하지 않고, Formatted Database Block에 직접 기록한다.
unrecoverable 옵션을 사용하면, redo/undo log에 기록하는 시간도 없앨 수 있다.
*/
/*
Case #7 : 정형화된 보고서에서 추출하여 로딩
Trigger를 이용하여 정형화된 보고서에서 데이타 로딩하는 예이다.
*/
-- 테이블 구조
CREATE TABLE EMP_NOM7 (
EMPNO NUMBER(4) NOT NULL,
ENAME CHAR(10),
JOB CHAR(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);
--Table Description 순서와 Control file의 Column 순서가 일치할 필요는 없다.
CREATE UNIQUE INDEX EMPIX_7 ON EMP_NOM7(EMPNO);
-- 콘트롤 파일(ulcase7.ctl)
LOAD DATA
INFILE 'ulcase7.dat'
DISCARDFILE 'ulcase7.dis'
APPEND
INTO TABLE EMP_NOM7
WHEN (57)='.'
TRAILING NULLCOLS
(
HIREDATE SYSDATE,
DEPTNO POSITION(1:2) INTEGER EXTERNAL(3) NULLIF DEPTNO=BLANKS,
JOB POSITION(7:14) CHAR TERMINATED BY WHITESPACE NULLIF JOB=BLANKS "UPPER(:JOB)",
MGR POSITION(28:31) INTEGER EXTERNAL TERMINATED BY WHITESPACE NULLIF MGR=BLANKS,
ENAME POSITION (34:41) CHAR TERMINATED BY WHITESPACE "UPPER(:ENAME)",
EMPNO INTEGER EXTERNAL TERMINATED BY WHITESPACE,
SAL POSITION(51) CHAR TERMINATED BY WHITESPACE "TO_NUMBER(:SAL,'$99,999.99')",
COMM INTEGER EXTERNAL ENCLOSED BY '(' AND '%' ":COMM * 100")
-- WHEN 조건에 의해서 57번 컬럼에 점(.)이 있는 경우만 로딩하고, 나머지는 Discard 파일에 기록된다.
-- TRAILING NULLCOLS에 의해 실제 필요한 컬럼수보다 데이타파일의 컬럼이 적은 경우 나머지는 Null로 등록한다.
-- NULLIF deptno=BLANKS deptno가 공백인 경우 Null로 등록
-- UPPER() 해당 필드값을 로딩 시 UPPER() 함수를 실행하여 로딩하라는 의미
-- comm 값은 '('와 '%'문자로 둘러싸여 있고, 로딩시 :comm * 100 을 수행하여 로딩하라는 의미
-- 데이타 파일(ulcase7.dat)
Today''s Newly Hired Employees
Dept Job Manager MgrNo Emp Name EmpNo Salary/Commission
---- ---------- --------- ----- ----------- ------- -------------------
20 Salesman Blake 7698
Shepard 8061 $1,600.00 (3%)
Falstaff 8066 $1,250.00 (5%)
Major 8064 $1,250.00 (14%)
30 Clerk Scott 7788
Conrad 8062 $1,100.00
Ford 7369
DeSilva 8063 $800.00
Manager King 7839
Provo 8065 $2,975.00
-- Trigger를 이용하여 앞 레코드의 데이타가 다음 레코드 로딩 시 이용되도록 한다. 즉, 두번째, 세번째 레코드의 Dept 필드는 공백이지만 첫번째 레코드의 20이 대신 로딩된다.
-- 실행 예
sqlldr userid=hr/hr control=ulcase7.ctl log=ulcase7.log
-- TRUNCATE TABLE EMP_NOM7;
SELECT * FROM EMP_NOM7;
-- Trigger 생성
CREATE OR REPLACE PACKAGE uldemo7 AS
last_deptno NUMBER;
last_job CHAR(9);
last_mgr NUMBER;
END uldemo7;
/
-- 이전 값들을 저장하기 위한 전역변수 개념으로 이해하면 된다.
CREATE OR REPLACE TRIGGER uldemo7_emp_nom7_insert
BEFORE INSERT ON emp_nom7
FOR EACH ROW
BEGIN
IF :new.deptno IS NOT NULL THEN
uldemo7.last_deptno := :new.deptno; --save value for later use
ELSE
:new.deptno := uldemo7.last_deptno; --use last valid value
END IF;
IF :new.job IS NOT NULL THEN
uldemo7.last_job := :new.job; --save value for later use
ELSE
:new.job := uldemo7.last_job; --use last valid value
END IF;
IF :new.mgr IS NOT NULL THEN
uldemo7.last_mgr := :new.mgr; --save value for later use
ELSE
:new.mgr := uldemo7.last_mgr; --use last valid value
END IF;
END;
/
-- emp 테이블에 insert가 일어나기 바로직전에 아래의 PL/SQL문장이 수행된다.
-- 새로운 값이 들어오면 uldemo7 패키지에 값을 저장하고,
-- Null 이면 uldemo7 패키지에 있는 값을 사용한다.
Case #8 : (8i이상) 파티션 테이블
파티션 테이블에 데이타를 로딩하는 예이다.
테이블 구조
create table lineitem (
l_orderkey number,
l_partkey number,
l_suppkey number,
l_linenumber number,
l_quantity number,
l_extendedprice number,
l_discount number,
l_tax number,
l_returnflag char,
l_linestatus char,
l_shipdate date,
l_commitdate date,
l_receiptdate date,
l_shipinstruct char(17),
l_shipmode char(7),
l_comment char(43)
)
partition by range (l_shipdate)
(
partition ship_q1 values less than (TO_DATE('01-APR-1996', 'DD-MON-YYYY')),
partition ship_q2 values less than (TO_DATE('01-JUL-1996', 'DD-MON-YYYY')),
partition ship_q3 values less than (TO_DATE('01-OCT-1996', 'DD-MON-YYYY')),
partition ship_q4 values less than (TO_DATE('01-JAN-1997', 'DD-MON-YYYY'))
);
- l_shipdate 컬럼을 Partition Key로 하여 4개의 파티션으로 구성되어 있다.
콘트롤 파일(ulcase8.ctl)
LOAD DATA
INFILE 'ulcase8.dat' "fix 129"
BADFILE 'ulcase8.bad'
TRUNCATE
INTO TABLE lineitem
PARTITION (ship_q1)
(l_orderkey position (1:6) char,
l_partkey position (7:11) char,
l_suppkey position (12:15) char,
l_linenumber position (16:16) char,
l_quantity position (17:18) char,
l_extendedprice position (19:26) char,
l_discount position (27:29) char,
l_tax position (30:32) char,
l_returnflag position (33:33) char,
l_linestatus position (34:34) char,
l_shipdate position (35:43) char,
l_commitdate position (44:52) char,
l_receiptdate position (53:61) char,
l_shipinstruct position (62:78) char,
l_shipmode position (79:85) char,
l_comment position (86:128) char)
- 데이타파일의 각 레코드가 129byte의 고정길이임을 지정한다.
- lineitem 테이블의 모든 데이타를 삭제한 후 로딩을 시작한다.
데이타 파일(ulcase8.dat)
1 151978511724386.60 7.04.0NO09-SEP-6412-FEB-9622-MAR-96DELIVER IN PERSONTRUCK iPBw4mMm7w7kQ zNPL i261OPP
1 2731 73223658958.28.09.06NO12-FEB-9628-FEB-9620-APR-96TAKE BACK RETURN MAIL 5wM04SNyl0AnghCP2nx lAi
1 3370 3713 810210.96 .1.02NO29-MAR-9605-MAR-9631-JAN-96TAKE BACK RETURN REG AIRSQC2C 5PNCy4mM
1 5214 46542831197.88.09.06NO21-APR-9630-MAR-9616-MAY-96NONE AIR Om0L65CSAwSj5k6k
1 6564 6763246897.92.07.02NO30-MAY-9607-FEB-9603-FEB-96DELIVER IN PERSONMAIL CB0SnyOL PQ32B70wB75k 6Aw10m0wh
1 7403 160524 31329.6 .1.04NO30-JUN-9614-MAR-9601 APR-96NONE FOB C2gOQj OB6RLk1BS15 igN
2 8819 82012441659.44 0.08NO05-AUG-9609-FEB-9711-MAR-97COLLECT COD AIR O52M70MRgRNnmm476mNm
3 9451 721230 41113.5.05.01AF05-SEP-9629-DEC-9318-FEB-94TAKE BACK RETURN FOB 6wQnO0Llg6y
3 9717 1834440788.44.07.03RF09-NOV-9623-DEC-9315-FEB-94TAKE BACK RETURN SHIP LhiA7wygz0k4g4zRhMLBAM
3 9844 1955 6 8066.64.04.01RF28-DEC-9615-DEC-9314-FEB-94TAKE BACK RETURN REG AIR6nmBmjQkgiCyzCQBkxPPOx5j4hB 0lRywgniP1297
실행 예
sqlldr userid=scott/tiger control=ulcase8.ctl log=ulcase8.log
Case #9 : (8i이상) LOBFILE (CLOB)
CLOB 데이타의 로딩 예이다.
테이블 구조
create table emp (
empno number(4) not null,
ename char(10),
job char(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2),
resume clob
);
콘트롤 파일(ulcase9.ctl)
LOAD DATA
INFILE *
INTO TABLE EMP
REPLACE
FIELDS TERMINATED BY ','
(EMPNO INTEGER EXTERNAL,
ENAME CHAR,
JOB CHAR,
MGR INTEGER EXTERNAL,
SAL DECIMAL EXTERNAL,
COMM DECIMAL EXTERNAL,
DEPTNO INTEGER EXTERNAL,
RES_FILE FILLER CHAR,
"RESUME" LOBFILE (RES_FILE) TERMINATED BY EOF NULLIF RES_FILE = 'NONE'
)
BEGINDATA
7782,CLARK ,MANAGER ,7839 ,2572.50, ,10,ulcase91.dat
7839,KING ,PRESIDENT, ,5500.00, ,10,ulcase92.dat
7934,MILLER ,CLERK ,7782 ,920.00 , ,10,ulcase93.dat
7566,JONES ,MANAGER ,7839 ,3123.75, ,20,ulcase94.dat
7499,ALLEN ,SALESMAN ,7698 ,1600.00,300.00 ,30,ulcase95.dat
7654,MARTIN,SALESMAN ,7698 ,1312.50,1400.00,30,ulcase96.dat
7658,CHAN ,ANALYST ,7566 ,3450.00, ,20,NONE
- 실제 Lobfile과 Mapping하기 위한 Lobfile의 이름을 추출한다. 즉, 첫번째 레코드의 경우 RES_FILE 필드에 ulcase91.dat 문자열이 읽혀지고, 이는 실제 데이타를 읽어 올 Lobfile의 파일명이다.
- RES_FILE에 지정된 Lobfile에서 데이타를 읽어서 RESUME 컬럼에 로딩한다. 데이타는 EOF를 만날 때까지 읽는다. RES_FILE에 'NONE'으로 지정된 경우는 NULL로 로딩된다.
실행 예
sqlldr userid=scott/tiger control=ulcase9.ctl log=ulcase9.log
데이타 파일(ulcase91.dat)
Resume for Mary Clark
Career Objective: Manage a sales team with consistent record breaking performance.
Education: BA Business University of Iowa 1992
Experience: 1992-1994 - Sales Support at MicroSales Inc.
Won "Best Sales Support" award in 1993 and 1994
1994-Present - Sales Manager at MicroSales Inc.
Most sales in mid-South division for 2 years
- ulcase91.dat 파일의 내용이 resume 컬럼에 통째로 들어간다.
Case #10 : (8i이상) REF Fields and VARRAYs
Reference Type과 Varray Type의 컬럼을 가진 테이블에 자료를 로딩하는 예이다.
테이블 구조
create type customer_type as object (
cust_no char(5),
name char(20),
addr char(20)
);
create table customers of customer_type
(primary key (cust_no))
object id primary key;
--customers 테이블의 컬럼은 cust_no, name, addr이고, cust_no는 PK로 사용된다.
create type item_type as object (
item varchar(50),
cnt number,
price number(7,2)
);
create type item_list_type as varray (1000) of item_type;
create table orders (
order_no char(5),
cust ref customer_type references customers,
item_list item_list_type
);
--customers 테이블의 reference 타입이다.
--item_type의 varray 타입이다. C language의 구조체 배열과 비슷한 개념이다.
콘트롤 파일(ulcase10.ctl)
LOAD DATA
INFILE *
CONTINUEIF THIS (1) = '*'
INTO TABLE customers
replace
fields terminated by ","
(
cust_no char,
name char,
addr char
)
INTO TABLE orders
replace
fields terminated by ","
(
order_no char,
cust_no FILLER char,
cust REF (CONSTANT 'CUSTOMERS', cust_no),
item_list_count FILLER char,
item_list varray count (item_list_count)
(
item_list column object
(
item char,
cnt char,
price char
)
)
)
BEGINDATA
*00001,Spacely Sprockets,15 Space Way,
*00101,00001,2,
*Sprocket clips, 10000, .01,
Sprocket cleaner, 10, 14.00
*00002,Cogswell Cogs,12 Cogswell Lane,
*00100,00002,4,
*one quarter inch cogs,1000,.02,
*one half inch cog, 150, .04,
*one inch cog, 75, .10,
Custom coffee mugs, 10, 2.50
- '*' 라인의 첫번째 컬럼에 '*' 문자가 있으면, 다음에 나오는 Line을 현재 Line의 연장으로 간주한다.
- orders 테이블의 cust 컬럼은 customers 테이블의 reference type이므로, 로딩 시 customers 테이블의 어떤 레코드와 레퍼런싱 할 것인지의 정보가 필요하다. 따라서 customers 테이블의 PK에 해당하는 cust_no를 읽어서 이를 이용해 customers 테이블과 레퍼런싱 한다.
- item_list 컬럼은 item_type의 varray로 C의 구조체 배열과 같은 개념이다. 여기서는 item, cnt, price를 로딩해야 하는데, 중요한 건 몇번을 로딩해야 하는지를 알아야 하는 것이다.
따라서, 먼저 item_list_count 를 구한 후, 이 값만큼 자료를 item_list 컬럼으로 로딩한다.
- 보라, 남색, 녹색 순
customers 테이블의 cust_no, name, addr 로 로딩된다.
orders 테이블의 order_no, cust 로 로딩된다.
orders 테이블의 item_list 로 로딩된다.
customers 테이블의 cust_no, name, addr 로 로딩된다.
orders 테이블의 order_no, cust 로 로딩된다.
orders 테이블의 item_list 로 로딩된다.
실행 예
sqlldr userid=scott/tiger control=ulcase10.ctl log=ulcase10.log
.
Posted by 홍반장
