SQL*Loader

http://wiki.gurubee.net/display/STUDY/SQL*Loader

데이터 로딩과 언로딩

#데이터 로딩툴

• SQL*Loader
• External 테이블 : external 테이블은 운영체제 파일을 데이터베이스 테이블처럼 조회할 수 있는 기능(9i이상사용,10g이상 테이블의 데이터를 추출해서 운영체제 파일을 생성할 수도 있다.)

#데이터 언로딩 기술

• flat file unload - 사용자가 정의한 포멧에 맞게 만들며 스프레드 시트와 같은 다른 유형에 호환가능한 결과도 만들수 있다.
• data pump unload - 바이너리포멧으로서 데이터 펌프툴과 external 테이블로 접근할 수 있다.

SQLLDR는 매우 짧은 시간에 대용량의 데이터를 로드하는데 사용할 수 있고 2가지 모드가 있다.

1	conventional path	데이터를 로드하는데 sql insert를 사용 buffer cache를 거쳐서 data file에 쓰게 됨
2	direct path	direct path모드에서 sql을 사용하지 않고 직접 메모리에 data block을 만들어서 해당 table에 저장 플랫 파일의 데이터를 읽으면서 sql엔진을 거치지않는다 언두를 생성하지도 않으며 어떤 경우에는 리두 또한 생성하지 않는다

sqlldr 도움말 $sqlldr SQL*Loader: Release 11.2.0.3.0 - Production on 화 11월 27 19:13:38 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 사용법: SQLLDR keyword=value [,키워드=값,...] 적절한 키워드: userid -- ORACLE 사용자 이름/비밀번호 control -- 제어 파일 이름입니다. log -- 로그 파일 이름입니다. bad -- 부적합한 파일 이름입니다. data -- 데이터 파일 이름 discard -- 폐기 파일 이름 discardmax -- 허용할 폐기 수 (기본값 all) skip -- 건너 뛸 논리 레코드 수 (기본값 0) load -- 로드할 논리 레코드 수 (기본값 all) errors -- 허용할 오류 수 (기본값 50) rows -- 기본 경로 바인드 배열 또는 직접 경로 데이터 저장 사이의 행 수 (기본값: 규약 경로 64, 직접 경로 전체) bindsize -- 기본 경로 바인드 배열 크기(바이트) (기본값 256000) silent -- 실행 중 메시지 숨기기(헤더,피드백,오류,폐기,분할 영역) direct -- 직접 경로 사용 (기본값 FALSE) parfile -- 매개변수 파일: 매개변수 사양을 포함하는 파일 이름 parallel -- 병렬 로드 수행 (기본값 FALSE) file -- 확장 영역을 할당할 파일 skip_unusable_indexes -- 사용할 수 없는 인덱스 또는 인덱스 분할 영역 허용 안함/허용 (기본값 FALSE) skip_index_maintenance -- 인덱스 유지 관리 안함, 영향을 받은 인덱스를 사용 불가로 표시 (기본값 FALSE) commit_discontinued -- 로드가 중단되는 경우 로드된 행 커밋 (기본값 FALSE) readsize -- 읽기 버퍼 크기 (기본값 1048576) external_table -- 로드를 위해 외부 테이블 사용: NOT_USED, GENERATE_ONLY, EXECUTE (기본값 NOT_USED) columnarrayrows -- 직접 경로 열 배열에 대한 행 수 (기본값 5000) streamsize -- 직접 경로 스트림 버퍼 크기(바이트) (기본값 256000) multithreading -- 직접 경로에서 다중 스레드 사용 resumable -- 현재 세션에 대한 재개를 사용 또는 사용 안함으로 설정합니다. (기본값 FALSE) resumable_name -- 재개 가능한 명령문 식별에 도움이 되는 텍스트 문자열 resumable_timeout -- RESUMABLE에 대한 대기 시간(초) (기본값 7200) date_cache -- 날짜 변환 캐시 크기(항목) (기본값 1000) no_index_errors -- 인덱스 오류 발생 시 로드 중단 (기본값 FALSE) 주: 명령행 매개변수는 위치 혹은 키워드로 지정될 수 있습니다. 위치 또는 키워드별입니다. 전자의 예는 'sqlldr scott/tiger foo'; 후자의 예는 'sqlldr control=foo' 하나는 이전 위치에 의해 매개변수를 지정할 수 있으나 매개변수가 키워드에 의해 지정한 이후에는 할 수 없습니다. 예를 들어, 'sqlldr scott/tiger control=foo logfile=log'는 허용되지만 'sqlldr scott/tiger control=foo log'는 위치가 맞더라도 허용되지 않습니다

_참조 : http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_params.htm#g1014550_

SQLLDR를 사용하기위해서는 contol file이 필요하다. 
컨트롤 파일에 포함 될 수 있는 내용

• 입력 대상 테이블에 대한 정보
• 레이아웃
• 데이터타입등 입력 데이터를 설명하는 정보
• 로드해야할 데이터

Control file 생성방법

1	LOAD DATA	데이터를 로드
2	INFILE *	컨트롤파일 자체내에 실제로드될 데이터가 있음을 알려줌, 데이터가 포함된 다른 파일명을 지정할수도 있다 ex) INFILE demo.dat
3	INTO TABLE	로딩할 테이블(DEPT)
4	FILEDS TERMINATED BY ','	입력데이터를 구분할 방법(컬럼구분자)
5	(DEPTNO,DNAME,LOC)	입력될 데이터의 컬럼 순서와 필드길이 (char타입은 디폴트가 255) ex)(DEPTNO,DNAME char(1000),LOCL)
6	BEGINDATA	sqlldr로 입력할 데이터의 설명부분이 끝난것과 실제데이터입력을 알람
7	10,SALES,VIRGINIA	실제데이터
8	20,ACCOUNTING,VIRGINIA	실제데이터
9	30,CONNSULTING,VIRGINIA	실제데이터
10	40,FINANCE,VIRGINIA	실제데이터

TEST
control 파일(demo1.ctl) 생성 LOAD DATA INFILE * INTO TABLE dept FIELDS TERMINATED BY ',' ( deptno ,dname ,loc ) BEGINDATA 10,ACCOUNTING,NEW YORK 20,RESEARCH,DALLAS 30,SALES,CHICAGO 40,OPERATIONS,BOSTON
a. 빈 DEPR 테이블 생성(demo1.ctl라는 컨트롤파일을 사용하기위해) SQL> create table dept (deptno number(2) constraint dept_pk primary key, dname varchar2(14), loc varchar2(13) ) / 테이블이 생성되었습니다.
b. 명령어 실행 $sqlldr userid=hun/imsi00 control=C:\book\ch15\demo1.ctl SQL*Loader: Release 11.2.0.3.0 - Production on 화 11월 27 20:28:02 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 커밋 시점에 도달 - 논리 레코드 개수 4
c. 결과 SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
d. 로그파일(demo1.log) SQL*Loader: Release 11.2.0.3.0 - Production on 화 11월 27 20:28:02 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 제어 파일: C:\book\ch15\demo1.ctl 데이터 파일: C:\book\ch15\demo1.ctl 부적합한 파일: C:\book\ch15\demo1.bad 폐기 파일: 지정 사항 없음 (모든 폐기된 레코드 허용) 로드할 건수: ALL 생략 건수: 0 허용 오류수: 50 바인드 배열: 64 행, 최대 256000 바이트 계속: 지정 사항 없음 사용된 경로: 규약 테이블 DEPT, 로드되었습니다 개개의 논리 레코드로부터 이 테이블에 적당한 Insert 옵션: INSERT 열 이름 위치 Len Term Encl 데이터유형 ------------------------------ ---------- ----- ---- ---- --------------------- DEPTNO FIRST * , CHARACTER DNAME NEXT * , CHARACTER LOC NEXT * , CHARACTER 테이블 DEPT: 4 행 로드되었습니다. 데이터 오류 때문에 0 행(이)가 로드되지 않았습니다 --오류가 발생해도 로그파일에서 확인 가능 모든 WHEN절이 실패하여 0 행(이)가 로드되지 않았습니다 모든 필드가 NULL이어서 0 행(이)가 로드되지 않았습니다 바인드 배열에 할당된 영역: 49536바이트(64 행) 읽기 버퍼 바이트: 1048576 생략된 논리 레코드의 합계: 0 읽어낸 논리 레코드의 합계: 4 거부된 논리 레코드의 합계: 0 폐기된 논리 레코드의 합계: 0 화 11월 27 20:28:02 2012에 실행 개시 화 11월 27 20:28:04 2012에 실행 종료 경과 시간: 00:00:02.24 CPU 시간: 00:00:00.05

#데이터 로딩과 관련한 SQLLDR FAQ

1. 로그파일의 '최대 길이 초과'가 왜 발생하는가?
2. 구분자를 포함하는 데이터 로드 방법
3. 고정 길이 포맷 데이터 로드 방법
4. 날짜 데이터 로드 방법
5. 함수를 사요한 데이터 로드 방법
6. 개행문자를 포함한 데이터 로드 방법
7. LOB 데이터 로드
8. lnline LOB 데이터 로딩
9. Out of Line LOB 데이터 로딩
10. 객체 컬럼에 LOB 데이터 로딩
11. 저장 프로시저에서 SQLLDR를 호출하는 방법

1. 로그파일의 '최대 길이 초과'가 왜 발생하는가?

Record 4:Rejected - Error on table DEPT, column DNAME. field in data file exceeds maximun legnth 위의 오류는 입력 레크도를 처리하는 과정에서 SQLLDR의 기본 데이타타입인 char(255)를 초과하는 문자열 데이타타입이 입력되었기 때문. char(255)를 초과하는 문자가 있다면 입력될 데이터의 컬럼 순서와 필드길이를 기술시 필드길이를 명시해 주어야한다.

2. 구분자를 포함하는 데이터 로드 방법

• 1) 콤마로 구분 된 데이터
• 2) 탭으로 구분된 데이터

TEST
1) 콤마로 구분 된 데이터 각 필드를 구분하기 위한 가장 일반적인 포멧은 CSV(comma-separated value)포맷이고 콤마로 필드 구분 만약에 문자열이 구분자를 포함하게 된다면(여기서는 ,로) 큰따옴표로 문자열을 감싸주면 된다. 그런데 문자열자체에 큰따옴표도 포함하게 된다면 '"'을 사용한다.
a. 컨트롤파일 수정 LOAD DATA infile * into table dept fields terminated by ',' optionally enclosed by '"' (DEPTNO,DNAME,LOC) begindata 10,sales,"virginia,USA" 20,Accountiong,"va,""USA""" --구분자인 ,를 포함하는 문자열는 " " 로 감싸여져 있으면 "를 포함하는 문자열은 " "로 한번더 감싸주고 있다. 30,consulting,virginia 40,Finance,virginia
b. SQLLDR실행 $sqlldr userid=hun/imsi00 control=C:\book\ch15\demo1.ctl SQL*Loader: Release 11.2.0.3.0 - Production on 화 11월 27 21:26:19 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 커밋 시점에 도달 - 논리 레코드 개수 3 커밋 시점에 도달 - 논리 레코드 개수 4
c. 실행결과 SQL> select *from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 sales virginia,USA 20 Accountiong va,"USA" 30 consulting virginia 40 Finance virginia

2) 탭으로 구분된 데이터 2가지 방법으로 구현 1. FILEDS TERMINATED BY WHITESPACE 2. FILEDS TERMINATED BY X'09' (16진수 포맷을 사용한 탭문자,ASCII에서 9는 탭문자)
1. FILEDS TERMINATED BY WHITESPACE
a. 컨트롤파일 수정 LOAD DATA infile * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY WHITESPACE (DEPTNO,DNAME,LOC) BEGINDATA 10 sales Virginia
b. SQLLDR실행 $sqlldr userid=hun/imsi00 control=C:\book\ch15\demo1.ctl
c. 실행결과 SQL> select *from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 sales Virginia
2. FILEDS TERMINATED BY X'09'
a. 컨트롤파일 수정 LOAD DATA infile * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY x'09' (DEPTNO, dummy1 filler, DNAME, dummy2 filler, LOC) BEGINDATA 10 sales Virginia
b. SQLLDR실행 $sqlldr userid=hun/imsi00 control=C:\book\ch15\demo1.ctl
c. 실행결과 안됨(0개inset)
d. 실행로그 SQL*Loader: Release 11.2.0.3.0 - Production on 수 11월 28 16:38:22 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 제어 파일: C:\book\ch15\demo1.ctl 데이터 파일: C:\book\ch15\demo1.ctl 부적합한 파일: C:\book\ch15\demo1.bad 폐기 파일: 지정 사항 없음 (모든 폐기된 레코드 허용) 로드할 건수: ALL 생략 건수: 0 허용 오류수: 50 바인드 배열: 64 행, 최대 256000 바이트 계속: 지정 사항 없음 사용된 경로: 규약 테이블 DEPT, 로드되었습니다 개개의 논리 레코드로부터 이 테이블에 적당한 Insert 옵션: REPLACE 열 이름 위치 Len Term Encl 데이터유형 ------------------------------ ---------- ----- ---- ---- --------------------- DEPTNO FIRST * WHT CHARACTER DUMMY1 NEXT * WHT CHARACTER (FILLER FIELD) DNAME NEXT * WHT CHARACTER DUMMY2 NEXT * WHT CHARACTER (FILLER FIELD) LOC NEXT * WHT CHARACTER 레코드 1: 기각됨 - 테이블 DEPT, 열 DUMMY2에 오류 논리 레코드가 종료하기 전에 열을 찾지 못했습니다 (TRAILING NULLCOLS 사용) 테이블 DEPT: 0 행 로드되었습니다. 데이터 오류 때문에 1 행(이)가 로드되지 않았습니다 모든 WHEN절이 실패하여 0 행(이)가 로드되지 않았습니다 모든 필드가 NULL이어서 0 행(이)가 로드되지 않았습니다 바인드 배열에 할당된 영역: 49536바이트(64 행) 읽기 버퍼 바이트: 1048576 생략된 논리 레코드의 합계: 0 읽어낸 논리 레코드의 합계: 1 거부된 논리 레코드의 합계: 1 폐기된 논리 레코드의 합계: 0 수 11월 28 16:38:22 2012에 실행 개시 수 11월 28 16:38:22 2012에 실행 종료 경과 시간: 00:00:00.05 CPU 시간: 00:00:00.01

3. 고정 길이 포맷 데이터 로드 방법

• SQLLDR로 로드하는 데 최적의 데이터 포멧
• 가장 빠른 로드 방법일 것.
• 로드해야할 데이터가 대용량이라면, 고정 위치 포맷으로 변환하는 것이 가장 최상의 방법
• 구분자를 갖는 파일보다 크기가 훨씬 클 수 있다.

  TEST
  a. entire_line 필드 추가 SQL> alter table dept add entire_line varchar2(29);
  b. 컨트롤파일 수정 LOAD DATA infile * into table dept replace (DEPTNO position(1:2), -- 고정위치 데이터를 로드하기 위해서 position 키워드를 사용해야한다. DNAME position(*:16), -- *는 바로앞 필드가 끝난 위치를 기준으로 1바이트 추가된 지점 (여기에선 * =3) LOC position(*) char(13), -- 각 필드의 길이를 명시해 줄 수도 있다. ENTIRE_LINE position(1:29) -- position절을 이용해서 구분자 없이 각 필드를 바이트로 계산해서 데이터를 임포트 할수도있다 ) begindata 10Accounting virginia,USA
  c. SQLLDR실행 후 결과 SQL> select *from dept; DEPTNO DNAME LOC ENTIRE_LINE ---------- -------------- ------------- ----------------------------- 10 Accounting vir ginia,USA 10Accounting virginia,USA

4. 날짜 데이터 로드 방법

• 날짜를 로딩하는 방법은 컨트롤 파일 내에 date 테이터타입을 사용해서 date mask를 명시할 수 있다.

TEST
a. last_updated 필드 추가 SQL> alter table dept add last_updated date;
b. 컨트롤파일 수정 LOAD DATA infile * into table dept replace fields terminated by ',' (DEPTNO, DNAME, LOC, LAST_UPDATED date 'dd/mm/yyyy' -- date mask는 데이터베이스에서 to_char와 to_date를 사용한 것과 같은 mask이다. ) begindata 10,salels,vig6inia,1/5/2000 20,Accounting,viginia,21/6/1999 30,Consulting,viginia,5/1/2000 40,Finance,viginia,15/3/2001
c. SQLLDR실행 후 결과 SQL> select *from dept; DEPTNO DNAME LOC ENTIRE_LINE LAST_UPD ---------- -------------- ------------- ----------------------------- -------- 10 salels vig6inia 00/05/01 20 Accounting viginia 99/06/21 30 Consulting viginia 00/01/05 40 Finance viginia 01/03/15

5. 함수를 사용한 데이터 로드 방법

• 함수를 사용할때는 반드시 SQL엔진을 경유해야 되기 때문에 direct path에서는 작동하지 않는다.

TEST
a. 컨트롤파일 수정 LOAD DATA infile * into table dept replace fields terminated by ',' TRAILING NULLCOLS -- NULL 허용 옵션 (DEPTNO, DNAME "upper(:dname)", -- 함수를 적용시키려면 " "를 추가하면 된다. LOC "upper(:loc)", LAST_UPDATED date 'dd/mm/yyyy' ) begindata 10,salels,vig6inia,1/5/2000 20,Accounting,viginia,21/6/1999 30,Consulting,viginia,5/1/2000 40,Finance,viginia,15/3/2001
b. SQLLDR실행 후 결과 SQL> select *from dept; DEPTNO DNAME LOC ENTIRE_LINE LAST_UPD ---------- -------------- ------------- ----------------------------- -------- 10 SALELS VIG6INIA 00/05/01 --dname,과 loc컬럼이 대문자로 입력 20 ACCOUNTING VIGINIA 99/06/21 30 CONSULTING VIGINIA 00/01/05 40 FINANCE VIGINIA 01/03/15

6. 개행문자를 포함한 데이터 로드 방법(p855)

• 오라클 8.1.6이상 버전에서 내장된 개행문자를 처리하는 옵션들
  1) 개행문자 대신 다른 문자를 사용 - 문장에 개행문자가 있을 때마다 \n을 넣음(\n을 CHR(10)으로 대체하는 SQL함수 사용)
  2) FIX 속성 사용 - 고정길이 플랫 파일 로드
  3) VAR 속성 사용 - 각 줄의 첫 몇 바이트가 그줄에 해당하는 길이를 명시하는 포맷을 사용
  4) STR 속성 사용 - 가변 길이 파일 로드

1) 개행문자 대신 다른 문자를 사용
a. comments 필드 추가 SQL> alter table dept add comments varchar2(4000);
a. 컨트롤파일 수정 LOAD DATA infile * into table dept replace fields terminated by ',' TRAILING NULLCOLS -- NULL 허용 옵션 (DEPTNO, DNAME "upper(:dname)", -- 함수를 적용시키려면 " "를 추가하면 된다. LOC "upper(:loc)", COMMENTS "replace(:comments,'\\n',chr(10))" -- \n을 두개의 문자열이 아닌 개행문자로 변환 ) begindata 10,salels,viginia,This is the sales\nOffice in virginia 20,Accounting,viginia,This is the Accounting\nOffice in Virginia 30,Consulting,viginia,This is the Consulting\nOffice in Virginia 40,Finance,viginia,This is the Finance\nOffice in VIrginia
b. SQLLDR실행 후 결과 hun@SOLTEST> select deptno, dname, comments from dept; DEPTNO DNAME COMMENTS ---------- -------------- ------------------------------------------------------------- 10 SALELS This is the sales Office in virginia 20 ACCOUNTING This is the Accounting Office in Virginia 30 CONSULTING This is the Consulting Office in Virginia 40 FINANCE This is the Finance Office in VIrginia
2) FIX 속성 사용
a. 컨트롤파일 수정 LOAD DATA infile demo.dat "fix 80" -- 입력데이터 파일이 각각 80바이트 레코드를가짐 into table dept replace fields terminated by ',' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", COMMENTS )
b. 8진수로변환한 dump파일 확인 r3_qas:/>od -c -w10 -v demo.dat -- od :8진수로 변환, -w10: 10개의 문자열만 출력 0000000 1 0 , s a l e l s , 0000012 v i g i n i a , T h 0000024 i s i s t h e 0000036 s a l e s \ n O f f 0000050 i c e i n v i r 0000062 g i n i a \n 0000074 0000106 0000120 2 0 , A c c o u n t 0000132 i n g , v i g i n i 0000144 a , T h i s i s 0000156 t h e A c c o u n 0000170 t i n g \ n O f f i 0000202 c e i n V i r g 0000214 i n i a \n 0000226 0000240 3 0 , C o n s u l t 0000252 i n g , v i g i n i 0000264 a , T h i s i s 0000276 t h e C o n s u l 0000310 t i n g \ n O f f i 0000322 c e i n V i r g 0000334 i n i a \n 0000346 . . .
c. SQLLDR실행 후 결과 hun@SOLTEST> select '"' || comments || '"' from dept; COMMENTS ------------------------------------------------------------- "This is the sales Office in virginia " "This is the Accounting Office in Virginia " "This is the Consulting Office in Virginia " "This is the Finance Office in VIrginia "
3) VAR 속성 사용
a. 컨트롤파일 수정 LOAD DATA infile demo.dat "var 3" -- 첫 번째 3바이트는 입력 레코드의 길이를 의미 into table dept replace fields terminated by ',' TRAILING NULLCOLS -- NULL 허용 옵션 (DEPTNO, DNAME "upper(:dname)", -- 함수를 적용시키려면 " "를 추가하면 된다. LOC "upper(:loc)", COMMENTS )
b. demo.dat 파일수정 5610,Sales,Virginia,This is the Sales --56바이트 , 유닉스에서는 056으로 Office in Virginia 6620,Accounting,viginia,This is the Accounting Office in Virginia 6630,Consulting,viginia,This is the Consulting Office in Virginia 6040,Finance,viginia,This is the Finance Office in VIrginia
c. SQLLDR실행 후 결과 ...
4) STR 속성 사용 내장된 개행문자를 가진 데이터를 로드하는 데 가장 유연한 방법 각 줄의 끝에 특수문잘르 가진 입력 파일 생성하여 사용할 수 있게 해준다.
a. STR확인 select utl_raw.cast_to_raw('|'||chr(10)) from dual; -- 유닉스에서 줄의종결표시는 chr(10), 특수문자표시는 | 라고 가정했을때 utl_raw.cast_to_row('|'||chr(10)) ---------------------------------- 7C0A -- STR = 7C0A select utl_raw.cast_to_raw('|'||chr(13)||chr(10)) from dual; -- 윈도우에서 줄의종결표시는 chr(10), 특수문자표시는 | 라고 가정했을때 UTL_RAW.CAST_TO_RAW('|'||CHR(13)||CHR(10)) ----------------------------------------------------------- 7C0D0A -- STR = 7C0D0A
b. 컨트롤파일 수정 LOAD DATA infile demo.dat "str X'7C0A'" -- 첫 번째 3바이트는 입력 레코드의 길이를 의미 into table dept replace fields terminated by ',' TRAILING NULLCOLS -- NULL 허용 옵션 (DEPTNO, DNAME "upper(:dname)", -- 함수를 적용시키려면 " "를 추가하면 된다. LOC "upper(:loc)", COMMENTS )
b. demo.dat 파일수정 10,Sales,Virginia,This is the Sales Office in Virginia| 20,Accounting,viginia,This is the Accounting Office in Virginia| 30,Consulting,viginia,This is the Consulting Office in Virginia| 40,Finance,viginia,This is the Finance Office in VIrginia|
c. SQLLDR실행 후 결과 SQL> select deptno, comments from dept; DEPTNO COMMENTS ---------- -------------------------------------- 10 This is the Sales Office in Virginia 20 This is the Accounting Office in Virginia 30 This is the Consulting Office in Virginia 40 This is the Finance Office in VIrginia

7. LOB 데이터 로드

• LOB : BLOB,CLOB 데이터타입
• LOB필드 로딩하기 위한 방법
  1) PL/SQL(DBMS_LOB패키지 사용)
  2) SQLLDR

1) PL/SQL로 LOB로딩 BFILE을 사용할 수 있도록 해주는 DBMS_LOB 패지지 프로시저(DB내에서 BLOB,CLOB컬럼으로 데이터를 옮기기 위해 운영체제 파일을 읽을수있도록해줌) LoadFromFile  LoadBLOBFromFile : BLOB컬럼에 로드된 데이터의 범위를 가리키는 프로시저 파라미터 후반부의 OUT파라미터를 제외하고 LoadFromFile과 차이가없다. LoadCLOBFromFile : LoadCLOBFromFile 루틴은 캐릭터 셋 변환 기능 _DBMS_LOB참조:http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_lob.htm#ARPLS600_
DBMS_LOB 패키지 프로시저 사용방법
1. DB에 DIRECTORY 객체 생성 필요한 권한 : CREATE ANY DIRECTORY create or replace directory dir1 as '/tmp/'; create or replace directory "dir2" as '/tmp/'; -- "dir2" => 대소문자 구분
2) BLOB or CLOB에 데이터를 로드
a. demo table 생성 create table demo ( id int primary key, theClob clob ) /
b. test.txt 파일 생성 host echo 'Hello World\!'>/tmp/test.txt
c. dbms_lob 패키지로 파일 업로드 declare l_clob clob; l_bfile bfile; begin insert into demo values( 1, empty_clob() ) -- CLOB을 EMPTY_CLOB()로 초기화 returning theClob into l_clob; l_bfile := bfilename( 'DIR1', 'test.txt'); -- BFIEL 객체 생성 dbms_lob.fileopen(l_bfile); -- LOB를 OPEN 함으로써 LOB를 읽을 수 있다. dbms_lob.loadfromfile(l_clob, l_bfile, -- INSERT했던 LOB locator에 운영체제파일 /tmp/test.txt의 전체내용을 로드 dbms_lob.getlength(l_bfile)); -- DBMS_LOB.GETLENGTH()를 사용해서 BFILE에 로드된 전체 바이트수를 LOADFROMFILE()루틴에 전달 dbms_lob.fileclose(l_bfile); -- CLOB을 로드하고 BFILE CLOSE end; /
d. 수행 결과 학인 sys@BWD> select dbms_lob.getlength(theClob), theClob from demo; DBMS_LOB.GETLENGTH(THECLOB) THECLOB --------------------------- -------------------- 13 Hello World!
2) SQLLDR로 LOB 데이터 로딩 1) inline 데이터인경우(LOB데이터가 다른 데이터 로우와 같은블록에 저장되는 경우) 2) out of line(LOB 데이터가 LOB세그먼트에 저장되는 경우) , SDF(secondary data file) 이라고도 함

8. lnline LOB 데이터 로딩

• LOB는 일반적으로 개행문자와 특수문자를 포함하고있다.
• 개행문자를 포함한 데이터 로드방법에서 다뤘던 네가지 방법중 하나로 TEST

a. DEPT테이블의 COMMENTS 컬럼을 큰 VARCHAR2필드 대신에 CLOB으로 수정 truncate table dept; alter table dept drop column comments; alter table dept add comments clob;

b. demo.dat 파일 수정 10,Sales,Virginia,This is the Sales Office in Virginia| 20,Accounting,viginia,This is the Accounting Office in Virginia| 30,Consulting,viginia,This is the Consulting Office in Virginia| 40,Finance,viginia,"This is the Finance Office in VIrginia, it has embedded comas and is much longer than the other comments field. If you feel the need to add double quoted text in here like this: ""You will need to double up those quotes!"" to preserve them in the stirng. This field keeps going for up to 1000000 bytes (because of the control file definition I Used) or until we hit the magic end of record marker, the | followed by an end of line - it is right here ->"|

c. 컨트롤 파일 수정 LOAD DATA infile demo.dat "str X'7C0D0A'" into table dept replace fields terminated by ',' optionally enclosed by '"' TRAILING NULLCOLS (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", COMMENTS char(1000000) )

d. 수행결과 COMMENTS ------------------------------------------------------------ This is the Sales Office in Virginia This is the Accounting Office in Virginia This is the Consulting Office in Virginia This is the Finance Office in VIrginia, it has embedded comas and is much longer than the other comments field. If you feel the need to add double quoted text in here like this: "You will need to double up those quotes!" to preserve them in the stirng. This field keeps going for up to 1000000 bytes (because of the control file definition I Used) or until we hit the magic end of record marker, the | followed by an end of line - it is right here ->

9. Out of Line LOB 데이터 로딩

• 로드해야 할 LOB 데이터 파일의 이름만 있는것(한 테이블 내에 구조화 된 데이터와 LOB 데이터가 함께있는게 아님)
• LOBFILE - 개행문자를 처리하는 방법보다 더 많은 유연성을 제공

LOBFILE 포멧

• 고정길이 필드( 예를들어 LOBFILE로 부터 100에서 1000바이트를 로드)
• 구분자를 가진 필드( enclosed by 구분자 또는 terminated by 구분자) – 가장 일반적인 형태
• 길이와 값을 가진 가변 길이 필드

TEST
a.lob_demo table 생성 create table lob_demo ( owner varchar2(255), time_stamp date, filename varchar2(255), data blob ) /
b. 컨트롤 파일 수정 LOAD DATA INFILE * REPLACE INTO TABLE LOB_DEMO ( owner position(17:25), time_stamp position(44:55) date "MON DD HH24:MI", filename position(57:100), data LOBFILE(filename) TERMINATED BY EOF ) BEGINDATA -rwxr-xr-x 1 oraqas dba 3380 Nov 16 16:04 .dbenv_r3_qas.csh -rwxr-xr-x 1 oraqas dba 3380 Nov 16 16:04 .dbenv_r3_qas.sh -rw-r--r-- 1 oraqas dba 3375 Aug 16 09:52 .dbenv_r3dev.sh -rw-r--r-- 1 oraqas dba 3363 Aug 16 09:52 .dbenv_r3prddb.csh -rw-r--r-- 1 oraqas dba 3377 Aug 16 09:52 .dbenv_r3prddb.sh -rwxrwxrwx 1 oraqas dba 175 Nov 16 16:04 .dbsrc_r3_qas.csh -rwxrwxrwx 1 oraqas dba 158 Nov 16 16:04 .dbsrc_r3_qas.sh -rw-r--r-- 1 oraqas dba 158 Aug 16 09:52 .dbsrc_r3dev.sh -rw-r--r-- 1 oraqas dba 175 Aug 16 09:52 .dbsrc_r3prddb.csh -rw-r--r-- 1 oraqas dba 158 Aug 16 09:52 .dbsrc_r3prddb.sh
c. 실행 결과 OWNER -------

10. 객체 컬럼에 LOB 데이터 로딩

• LOB와 복합객체 타입을 가진 테이블에 데이터를 로드
• 복합객체타입은 이미지처리를 사용할 때 가장많이 나타난다.
• 이미지 처리는 복합 객체 타입인 ORDSYS.ORDIMAGE를 사용해서 실행

TEST
a. image_load 테이블 생성 create table image_load( id number, name varchar2(255), image ordsys.ordimage ) /
hun@SOLTEST> desc image_load; 이름 널? 유형 ----------------------------------------------------- -------- ------------------------------------ ID NUMBER NAME VARCHAR2(255) IMAGE ORDSYS.ORDIMAGE desc ordsys.ordimage; 인수명 유형 기본 내부/외부? ------------------------------ ----------------------- --------- -------- CTX RAW IN/OUT LOCAL_DATA BLOB IN/OUT NOCOPY SOURCE_TYPE VARCHAR2 IN SOURCE_LOCATION VARCHAR2 IN SOURCE_NAME VARCHAR2 IN FORMAT VARCHAR2 OUT MIME_TYPE VARCHAR2 OUT . . . desc ordsys.ordsource; NAME Null? Type ------------------ ----- -------------- LOCALDATA BLOB SRCTYPE VARCHAR2(4000) SRCLOCATION VARCHAR2(4000) SRCNAME VARCHAR2(4000) . . .
a. 컨트롤파일 수정 LOAD DATA INFILE * INTO TABLE image_load REPLACE FIELDS TERMINATED BY ',' ( ID, NAME, file_name FILLER, IMAGE column object -- 컬럼의 이름이 아닌 컬럼 이름의 일부라는것을 SQLLDR에 알려준다. ( SOURCE column object ( LOCALDATA LOBFILE(file_name) TERMINATED BY EOF NULLIF file_name ='NONE' -- SQLLDR에 FILE_NAME필드가 NONE문자를 포함한경우, 객체컬럼에 NULL을 로드하도록한다. ) ) ) BEGINDATA 1,icons,icons.gif I have introduced two new constructs here:
begin for c in (select * from image_load) loop c.image.setproperties; -- SETPROPERTIES는 ORDSYS.ORDIMAGE타입으로 제공되는 이미지 자체를 처리하고, 객체의 나머지 속성을 적합한 값으로 변경하는 객체메서드 end loop; end; /

11. 저장 프로시저에서 SQLLDR를 호출하는 방법

• 저장 프로시저에서는 SQLLDR을 호출할 수 없다.

#SQLLDR 사용 시 주의사항

1. TRUNCATE가 다르게 동작하는 것처럼 보이는 현상

truncate table t reuse storage reuse storage 옵션은 free space라는 표시만 해두고 할당된 익스텐트를 반환하지 않는다.

2. SQLLDR 기본값은 CHAR(255)

Record N: Rejected - Error on table T, column C. Field in data file exceeds maximum length SQLLDR의 기본 데이터 타입과 길이가 CHAR(255), 더길게사용했을때 발생

3. 명령어는 컨트롤 파일보다 더 우선시됨

• SQLLDR의 다양한 옵션은 컨트롤 파일 내 또는 명령어 라인에 사용할 수 있다. ex)DATA=FILENAME, INFILE FILENAME
• 명령어는 컨트롤 파일내의 어떠한 옵션보다 우선시 되기때문에 컨트롤 파일내의 실제 사용된 옵션만을 믿어서는 안된다.

#SQLLDR 정리

• 데이터 로딩의 많은 영역과 일반적으로 발생하는 문제를 다루었다.
• direct path로더를 사용한 대용량 데이터 로드에 대해 간단히 언급했다.
• SQLLDR를 사용할 때 자주 발생하는 문제의 답을 제시했다.

문서정보

• 이 문서는 구루비에서 작성하였습니다.
• 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• 출처 : http://wiki.gurubee.net/display/STUDY/SQL*Loader?
• 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.