| 1 | conventional path | 데이터를 로드하는데 sql insert를 사용 buffer cache를 거쳐서 data file에 쓰게 됨 |
| 2 | direct path | direct path모드에서 sql을 사용하지 않고 직접 메모리에 data block을 만들어서 해당 table에 저장 플랫 파일의 데이터를 읽으면서 sql엔진을 거치지않는다 언두를 생성하지도 않으며 어떤 경우에는 리두 또한 생성하지 않는다 |
| sqlldr 도움말 {code:sql} $sqlldr |
SQL*Loader: Release 11.2.0.3.0 - Production on 화 11월 27 19:13:38 2012
Copyright © 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이 필요하다.
컨트롤 파일에 포함 될 수 있는 내용
* 입력 대상 테이블에 대한 정보
* 레이아웃
* 데이터타입등 입력 데이터를 설명하는 정보
* 로드해야할 데이터
h3. 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| 실제데이터 |
||h5. TEST||
|h3. control 파일(demo1.ctl) 생성
{code:sql}
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
|
| h3. a. 빈 DEPR 테이블 생성(demo1.ctl라는 컨트롤파일을 사용하기위해) {code:sql} SQL> create table dept (deptno number(2) constraint dept_pk primary key, dname varchar2(14), loc varchar2(13) ) / 테이블이 생성되었습니다. {code} |
| h3. b. 명령어 실행 {code:sql} $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 © 1982, 2011, Oracle and/or its affiliates. All rights reserved. 커밋 시점에 도달 - 논리 레코드 개수 4 {code} |
| h3. c. 결과 {code:sql} SQL> select * from dept; |
DEPTNO DNAME LOC
|
|
|h3. d. 로그파일(demo1.log)
{code:sql}
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
|
1. 로그파일의 '최대 길이 초과'가 왜 발생하는가?
2. 구분자를 포함하는 데이터 로드 방법
3. 고정 길이 포맷 데이터 로드 방법
4. 날짜 데이터 로드 방법
5. 함수를 사요한 데이터 로드 방법
6. 개행문자를 포함한 데이터 로드 방법
7. LOB 데이터 로드
8. lnline LOB 데이터 로딩
9. Out of Line LOB 데이터 로딩
10. 객체 컬럼에 LOB 데이터 로딩
11. 저장 프로시저에서 SQLLDR를 호출하는 방법
| {code:sql} Record 4:Rejected - Error on table DEPT, column DNAME. field in data file exceeds maximun legnth {code} 위의 오류는 입력 레크도를 처리하는 과정에서 SQLLDR의 기본 데이타타입인 char(255)를 초과하는 문자열 데이타타입이 입력되었기 때문. char(255)를 초과하는 문자가 있다면 입력될 데이터의 컬럼 순서와 필드길이를 기술시 필드길이를 명시해 주어야한다. |
| h5. TEST | |
|---|---|
| h4. 1) 콤마로 구분 된 데이터 각 필드를 구분하기 위한 가장 일반적인 포멧은 CSV(comma-separated value)포맷이고 콤마로 필드 구분 만약에 문자열이 구분자를 포함하게 된다면(여기서는 ,로) 큰따옴표로 문자열을 감싸주면 된다. 그런데 문자열자체에 큰따옴표도 포함하게 된다면 '"'을 사용한다. | h4. a. 컨트롤파일 수정 {code:sql} 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 {code} |
| h4. b. SQLLDR실행 {code:sql} $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 © 1982, 2011, Oracle and/or its affiliates. All rights reserved. 커밋 시점에 도달 - 논리 레코드 개수 3 커밋 시점에 도달 - 논리 레코드 개수 4 {code} | |
| h4. c. 실행결과 {code:sql} SQL> select *from dept; |
DEPTNO DNAME LOC
|
|
||
||h4. 2) 탭으로 구분된 데이터
* 2가지 방법으로 구현
1. FILEDS TERMINATED BY WHITESPACE
2. FILEDS TERMINATED BY X'09' (16진수 포맷을 사용한 탭문자,ASCII에서 9는 탭문자)
||1. FILEDS TERMINATED BY WHITESPACE||
|h4. a. 컨트롤파일 수정
{code:sql}
LOAD DATA
infile *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY WHITESPACE
(DEPTNO,DNAME,LOC)
BEGINDATA
10 sales Virginia
|
| h4. b. SQLLDR실행 {code:sql} $sqlldr userid=hun/imsi00 control=C:\book\ch15\demo1.ctl {code} |
| h4. c. 실행결과 {code:sql} SQL> select *from dept; |
DEPTNO DNAME LOC
|
||2. FILEDS TERMINATED BY X'09'||
|h4. a. 컨트롤파일 수정
{code:sql}
LOAD DATA
infile *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY x'09'
(DEPTNO, dummy1 filler, DNAME, dummy2 filler, LOC)
BEGINDATA
10 sales Virginia
|
| h4. b. SQLLDR실행 {code:sql} $sqlldr userid=hun/imsi00 control=C:\book\ch15\demo1.ctl {code} |
| h4. c. 실행결과 {code:sql} 안됨(0개inset) {code} |
| h4. d. 실행로그 {code:sql} SQL*Loader: Release 11.2.0.3.0 - Production on 수 11월 28 16:38:22 2012 |
Copyright © 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 데이터유형
레코드 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
|
|
h3. 3. 고정 길이 포맷 데이터 로드 방법
* SQLLDR로 로드하는 데 최적의 데이터 포멧
* 가장 빠른 로드 방법일 것.
* 로드해야할 데이터가 대용량이라면, 고정 위치 포맷으로 변환하는 것이 가장 최상의 방법
* 구분자를 갖는 파일보다 크기가 훨씬 클 수 있다.
||h5. TEST ||
|h4. a. entire_line 필드 추가
{code:sql}
SQL> alter table dept add entire_line varchar2(29);
| h4. b. 컨트롤파일 수정 {code:sql} 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 {code} |
| h4. c. SQLLDR실행 후 결과 {code:sql} SQL> select *from dept; |
DEPTNO DNAME LOC ENTIRE_LINE
|
h3. 4. 날짜 데이터 로드 방법
* 날짜를 로딩하는 방법은 컨트롤 파일 내에 date 테이터타입을 사용해서 date mask를 명시할 수 있다.
||h5. TEST ||
|h4. a. last_updated 필드 추가
{code:sql}
SQL> alter table dept add last_updated date;
| h4. b. 컨트롤파일 수정 {code:sql} 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 {code} |
| h4. c. SQLLDR실행 후 결과 {code:sql} SQL> select *from dept; |
DEPTNO DNAME LOC ENTIRE_LINE LAST_UPD
|
h3. 5. 함수를 사용한 데이터 로드 방법
* 함수를 사용할때는 반드시 SQL엔진을 경유해야 되기 때문에 direct path에서는 작동하지 않는다.
||h5. TEST ||
|h4. a. 컨트롤파일 수정
{code:sql}
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
|
| h4. b. SQLLDR실행 후 결과 {code:sql} SQL> select *from dept; |
DEPTNO DNAME LOC ENTIRE_LINE LAST_UPD
|
h3. 6. 개행문자를 포함한 데이터 로드 방법(p855)
* 오라클 8.1.6이상 버전에서 내장된 개행문자를 처리하는 옵션들
1) 개행문자 대신 다른 문자를 사용 - 문장에 개행문자가 있을 때마다 \n을 넣음(\n을 CHR(10)으로 대체하는 SQL함수 사용)
2) FIX 속성 사용 - 고정길이 플랫 파일 로드
3) VAR 속성 사용 - 각 줄의 첫 몇 바이트가 그줄에 해당하는 길이를 명시하는 포맷을 사용
4) STR 속성 사용 - 가변 길이 파일 로드
||h5. 1) 개행문자 대신 다른 문자를 사용 ||
|h4. a. comments 필드 추가
{code:sql}
SQL> alter table dept add comments varchar2(4000);
| h4. a. 컨트롤파일 수정 {code:sql} 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 {code} |
| h4. b. SQLLDR실행 후 결과 {code:sql} hun@SOLTEST> select deptno, dname, comments from dept; |
DEPTNO DNAME COMMENTS
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
|
||h5. 2) FIX 속성 사용 ||
|h4. a. 컨트롤파일 수정
{code:Sql}
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
)
|
| h4. b. 8진수로변환한 dump파일 확인 {code:sql} 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 . . . |
|
|h4. c. SQLLDR실행 후 결과
{code:sql}
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 "
|
| h5. 3) VAR 속성 사용 | |||||
|---|---|---|---|---|---|
| h4. a. 컨트롤파일 수정 {code:sql} 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 ) {code} | |||||
| h4. b. demo.dat 파일수정 {code:sql} 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 {code} | |||||
| h4. c. SQLLDR실행 후 결과 {code:sql} ... {code} | |||||
| h5. 4) STR 속성 사용 * 내장된 개행문자를 가진 데이터를 로드하는 데 가장 유연한 방법 * 각 줄의 끝에 특수문잘르 가진 입력 파일 생성하여 사용할 수 있게 해준다. | |||||
| h4. a. STR확인 {code:sql} 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))
|
|h4. b. 컨트롤파일 수정
{code:sql}
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
)
|
| h4. b. demo.dat 파일수정 {code:sql} 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|
|
|h4. c. SQLLDR실행 후 결과
{code:sql}
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
|
| h4. 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 {code:sql} create or replace directory dir1 as '/tmp/'; create or replace directory "dir2" as '/tmp/'; -- "dir2" => 대소문자 구분 {code} | |||
| h3. 2) BLOB or CLOB에 데이터를 로드 | |||
| h4. a. demo table 생성 {code:sql} create table demo ( id int primary key, theClob clob ) {code} / | h4. b. test.txt 파일 생성 {code:sql} host echo 'Hello World\!'>/tmp/test.txt {code} | h4. c. dbms_lob 패키지로 파일 업로드 {code:sql} 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; / {code} | h5. d. 수행 결과 학인 {code:sql} sys@BWD> select dbms_lob.getlength(theClob), theClob from demo; |
DBMS_LOB.GETLENGTH(THECLOB) THECLOB
|
||h4. 2) SQLLDR로 LOB 데이터 로딩
1) inline 데이터인경우(LOB데이터가 다른 데이터 로우와 같은블록에 저장되는 경우)
2) out of line(LOB 데이터가 LOB세그먼트에 저장되는 경우) , SDF(secondary data file) 이라고도 함 ||
h3. 8. lnline LOB 데이터 로딩
* LOB는 일반적으로 개행문자와 특수문자를 포함하고있다.
* 개행문자를 포함한 데이터 로드방법에서 다뤘던 네가지 방법중 하나로 TEST
|h4. a. DEPT테이블의 COMMENTS 컬럼을 큰 VARCHAR2필드 대신에 CLOB으로 수정
{code:sql}
truncate table dept;
alter table dept drop column comments;
alter table dept add comments clob;
| h4. b. demo.dat 파일 수정 {code:sql} 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 ->"|
|
|h4. c. 컨트롤 파일 수정
{code:sql}
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)
)
| h4. d. 수행결과 {code:sql} 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 ->
|
h3. 9. Out of Line LOB 데이터 로딩
* 로드해야 할 LOB 데이터 파일의 이름만 있는것(한 테이블 내에 구조화 된 데이터와 LOB 데이터가 함께있는게 아님)
* LOBFILE - 개행문자를 처리하는 방법보다 더 많은 유연성을 제공
LOBFILE 포멧
* 고정길이 필드( 예를들어 LOBFILE로 부터 100에서 1000바이트를 로드)
* 구분자를 가진 필드( enclosed by 구분자 또는 terminated by 구분자) -- 가장 일반적인 형태
* 길이와 값을 가진 가변 길이 필드
||TEST||
|h4. a.lob_demo table 생성
{code:sql}
create table lob_demo
( owner varchar2(255),
time_stamp date,
filename varchar2(255),
data blob
)
/
| h4. b. 컨트롤 파일 수정 {code:sql} 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 -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 {code} |
| h4. c. 실행 결과 {code:sql} OWNER --- |
|
h3. 10. 객체 컬럼에 LOB 데이터 로딩
* LOB와 복합객체 타입을 가진 테이블에 데이터를 로드
* 복합객체타입은 이미지처리를 사용할 때 가장많이 나타난다.
* 이미지 처리는 복합 객체 타입인 ORDSYS.ORDIMAGE를 사용해서 실행
||TEST||
|h4. a. image_load 테이블 생성
{code:sql}
create table image_load(
id number,
name varchar2(255),
image ordsys.ordimage
)
/
| h4. {code:sql} hun@SOLTEST> desc image_load; 이름 널? 유형 - ID NUMBER NAME VARCHAR2(255) IMAGE ORDSYS.ORDIMAGE |
desc ordsys.ordimage;
인수명 유형 기본 내부/외부?
desc ordsys.ordsource;
NAME Null? Type
|
|h4. a. 컨트롤파일 수정
{code:sql}
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:
| {code:sql} begin for c in (select * from image_load) loop c.image.setproperties; -- SETPROPERTIES는 ORDSYS.ORDIMAGE타입으로 제공되는 이미지 자체를 처리하고, 객체의 나머지 속성을 적합한 값으로 변경하는 객체메서드 end loop; end; / {code} |
| {code:sql} truncate table t reuse storage {code} * reuse storage 옵션은 free space라는 표시만 해두고 할당된 익스텐트를 반환하지 않는다. |
| {code:sql} Record N: Rejected - Error on table T, column C. Field in data file exceeds maximum length {code} * SQLLDR의 기본 데이터 타입과 길이가 CHAR(255), 더길게사용했을때 발생 |