Database

SQL*Loader

steloflute 2017. 2. 14. 23:30

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



데이터 로딩과 언로딩

#데이터 로딩툴

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

#데이터 언로딩 기술

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

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

1conventional path데이터를 로드하는데 sql insert를 사용
buffer cache를 거쳐서 data file에 쓰게 됨
2direct pathdirect 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 생성방법

1LOAD DATA
  • 데이터를 로드
2INFILE *
  • 컨트롤파일 자체내에 실제로드될 데이터가 있음을 알려줌,
  • 데이터가 포함된 다른 파일명을 지정할수도 있다 ex) INFILE demo.dat
3INTO TABLE
  • 로딩할 테이블(DEPT)
4FILEDS TERMINATED BY ','
  • 입력데이터를 구분할 방법(컬럼구분자)
5(DEPTNO,DNAME,LOC)
  • 입력될 데이터의 컬럼 순서와 필드길이 (char타입은 디폴트가 255)
    ex)(DEPTNO,DNAME char(1000),LOCL)
6BEGINDATA
  • sqlldr로 입력할 데이터의 설명부분이 끝난것과 실제데이터입력을 알람
710,SALES,VIRGINIA실제데이터
820,ACCOUNTING,VIRGINIA실제데이터
930,CONNSULTING,VIRGINIA실제데이터
1040,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를 사용할 때 자주 발생하는 문제의 답을 제시했다.

문서정보