제어파일에 데이터가 들어가 있는 경우
- DEPT_TEST 테이블에 insert하는 예제 입니다.
- 각 데이터의 레코드의 필드를 ','로 구분을 했습니다
============== dept_test.ctl 시작 ==============
LOAD DATA
INFILE *
INTO TABLE DEPT_TEST
FIELDS TERMINATED 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"
============== dept_test.ctl 끝 ==============
- 위 부분을 dept_test.ctl파일로 저장을 합니다.
- SQL*Loader을 실행 시킵니다.
C:\>sqlldr userid=scott/tiger control='C:\dept_test.ctl'
SQL*Loader: Release 8.1.6.0.0 - Production on 일 Dec 16 11:59:16 2001
커밋 시점에 도달 - 논리 레코드 개수 6
커밋 시점에 도달 - 논리 레코드 개수 7
- 데이터가 INSERT 되었는지 확인 합니다.
- dept_test.log파일이 생겼는지 확인해 봅니다.
C:\>SQLPLUS scott/tiger
SQL>SELECT * FROM dept_test;
DEPTNO DNAME LOC
------- -------------- -----------
12 RESEARCH SARATOGA
10 ACCOUNTING CLEVELAND
11 ART SALEM
13 FINANCE BOSTON
21 SALES PHILA
22 SALES ROCHESTER
42 "INT'L" "SAN FRAN"
제어파일과 데이터 파일이 분리된 경우
- Control File 부분은 dept.ctl로 저장 합니다.
- Data File 부분은 dept.dat로 저장을 합니다.
------------ dept.ctl 시작 ------------
LOAD DATA
INFILE 'dept.dat' -- Data File을 지정 합니다.
APPEND -- 기존에 데이터에 새로운 데이터를 추가 합니다.
INTO TABLE DEPT_TEST
FIELDS TERMINATED BY ','
(DEPTNO, DNAME, LOC)
------------ dept.ctl 끝 ------------
------------ dept.dat 시작 ------------
50,ACCOUNTING,NEW_YORK
60,RESEARCH,DALLAS
70,OPERATIONS,BOSTON
------------ dept.dat 시작 ------------
- SQL*Loader을 실행 시킵니다.
C:\>sqlldr userid=scott/tiger control='C:\dept.ctl'
SQL*Loader: Release 8.1.6.0.0 - Production on 일 Dec 16 12:08:26 2001
(c) Copyright 1999 Oracle Corporation. All rights reserved.
커밋 시점에 도달 - 논리 레코드 개수 2
커밋 시점에 도달 - 논리 레코드 개수 3
- 데이터가 INSERT 되었는지 확인 합니다.
- 기존에 데이터에 새로운 데이터가 추가 된 것을 확인 할 수 있습니다.
SQL> SELECT * FROM dept_test;
DEPTNO DNAME LOC
---------- -------------- ------------
12 RESEARCH SARATOGA
10 ACCOUNTING CLEVELAND
11 ART SALEM
13 FINANCE BOSTON
21 SALES PHILA
22 SALES ROCHESTER
42 "INT'L" "SAN FRAN"
50 ACCOUNTING NEW_YORK
60 RESEARCH DALLAS
70 OPERATIONS BOSTON
10 개의 행이 선택되었습니다.
SQL*Loader의 다른 사용법은 아래 링크를 참조해 보세용.
http://www.oracle.co.kr/support/web_supports/rcnote/rcnote103/rn103_utl_02.html
Case #7 : 정형화된 보고서에서 추출하여 로딩
Trigger를 이용하여 정형화된 보고서에서 데이타 로딩하는 예이다.
테이블 구조
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)
);
--Table Description 순서와 Control file의 Column 순서가 일치할 필요는 없다.
create unique index empix on emp(empno);
콘트롤 파일(ulcase7.ctl)
LOAD DATA
INFILE 'ulcase7.dat'
DISCARDFILE 'ulcase7.dis'
APPEND
INTO TABLE emp
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=scott/tiger control=ulcase7.ctl log=ulcase7.log
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_insert
BEFORE INSERT ON emp
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 패키지에 있는 값을 사용한다.
SQL*Loader 4000 byte 이상의 CLOB 데이터를 로드하는 방법
본 자료는 SQL*Loader로 4000 byte 이상의 CLOB 데이터를 로드하는 방법에 대해서 설명합니다.
오라클 릴리스 8.1부터 LOB형의 열에 데이터를 로드하는 것이 가능하게 되었습니다.
# 릴리스 8.0에서는
SQL*Loader를 사용하여 LOB형의 열에 데이터를 로드할 수 없습니다.
그 대신에 OCILobLoadFromFile() 또는 DBMS_LOB.LOADFROMFILE()를 사용해야 한다.
CLOB의 데이터를 로드하는 경우도 데이터형 CHAR를 사용하는 것이 가능합니다.
아래는 제어 파일의 기술예입니다.
테이블 TEST
=========================================================
create table test (id varchar2(1),
dt clob );
=========================================================
제어파일
=========================================================
load data
infile 'C:\loadtest.dat'
into table TEST
fields terminated by ',' optionally enclosed by '"'
(id char(1),
dt char(10000))
=========================================================
다만 개행을 포함한 경우는 VAR 옵션을 지정할 필요가 있습니다. VAR 옵션의 자세한 것은 SQL*Loader 개행 코드를 LOAD 하는 방법("var"옵션) 를 참조.
또한 LOBFILE를 사용하면 VAR 옵션을 지정하지 않아도 세컨다리·데이터·파일에 개행을 포함할 수 있습니다.
LOBFILE에 대한 자세한 것은, 「Oracle8i 유틸리티·가이드(J00925-01)」p.3-19 에 있습니다.
구체적인 예는, 동메뉴얼 p.4-39 사례 9:LOBFILE의 로드(CLOB)에도 있습니다만,
간단한 예를 아래와 같이에 기술합니다.
제어파일
=========================================================
load data
infile 'C:\loadtest.dat'
into table TEST
fields terminated by ','
( id terminated by ',' enclosed by '"',
fname filler,
dt LOBFILE(fname) terminated by eof
)
=========================================================
데이터파일 C:\loadtest.dat
=========================================================
"1",C:\loadtest1.dat
"2",C:\loadtest2.dat
=========================================================
# 세칸다리·데이터·파일 C:loadtest1.dat 및 C:loadtest2.dat 는,
# TEST표의 DT열에 들어가는 값이 기술된 텍스트 파일입니다.
*** 주의 1 ***
SQL*Loader는 LOB 필드를 바인드 배열에 삽입한 다음에 격납하기 때문에 LOB 필드의 처리에 에러가 있는 경우는, LOB 필드가 공백이 됩니다.
*** 주의 2 ***
64 KB를 넘는 physical record를 로드하는 경우는 READSIZE 파라미터를 사용해보다 큰 physical record·사이즈를 지정해야한다.
자세한 것은, 「Oracle8i 유틸리티·가이드(J00925-01)」p.3-17 (을)를 참조해 주세요.(p.3-17 에는, 그 외의 제한 사항도 기술되고 있으므로 로드시에 문제가 발생했을 경우는, 일독해 주시는 것을 추천 )
출처 : http://cafe.naver.com/helpjavatip.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=271