by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

14S_SQL*Loader




1. 데이터 로딩방식

  1. Conventional path : SQL insert를 사용하는 방식, 리두/언두 생성됨, 속도 느림
  2. Direct paht : 데이터베이스 블록에 데이터를 직접 쓰는 방식, 리두/언두 생성안됨, 속도 빠름

2.실행화면

3. 컨트롤파일(control file)

LOAD DATA 새 데이터 로드가 시작됨을 의미
INFILE * * : 컨트롤 파일에 입력할 데이터가 있음, 파일명 : 외부 데이터 파일 지정
BADFILE 'TEST.BAD' 거부된 레코드를 배치할 파일명 지정(레코드 형식, 제약조건 등이 맞지 않는 경우)
DISCARDFILE 'TEST.DSC' 폐기된 레코드를 배치할 파일명 지정(WHEN 조건 등에 맞지 않아 입력시 제외 된 파일)
REPLACE REPLACE : 데이터 삭제후 삽입(DELETE), APPEND : 기존 데이터에 추가, INSERT : 빈 테이블에 데이터 추가(기본값), TRUNCATE : 데이터 삭제 후 삽입
INTO TABLE DEPT 데이터를 저장할 테이블 지정
FIELDS TERMINATED BY ',' 데이터 필드의 종결문자 지정
(DEPTNO, DNAME, LOC ) 입력 필드명 지정
BEGINDATA 입력할 데이터 시작
1O, Sales, virginia 입력데이터(기본데이터 타입:char(255))
20, Accounting, virginia  
30, Consulting, virginia  
40, Finance, virginia  

4. 테스트1(INSERT 오류)

SQL> create table dept
  2  ( dept number(2) constraint dept_pk primary key,
  3  dname varchar2(14),
  4  loc varchar2(13)
  5  )
  6  /

테이블이 생성되었습니다.


[oracle@mydream test]$ cat demo.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO, DNAME, LOC )
BEGINDATA
10, sales, test
20, Accounting, test
30, Consulting, test
40, Finance, test



[oracle@mydream ~]$ sqlldr genie/genie control=demo.ctl

SQL*Loader: Release 11.2.0.1.0 - Production on 금 12월 26 12:03:18 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-601: INSERT 옵션을 사용하려면 테이블이 비어 있어야 합니다.  DEPT 테이블에 오류


[oracle@mydream test]$ cat demo.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
TRUNCATE
FIELDS TERMINATED BY ','
(DEPTNO, DNAME, LOC )
BEGINDATA
10, sales, test
20, Accounting, test
30, Consulting, test
40, Finance, test
[oracle@mydream test]$ sqlldr genie/genie control=demo.ctl

SQL*Loader: Release 11.2.0.1.0 - Production on 월 12월 29 14:13:30 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

커밋 시점에 도달 - 논리 레코드 개수 4

[oracle@mydream test]$ cat demo.log

SQL*Loader: Release 11.2.0.1.0 - Production on 월 12월 29 14:13:30 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

제어 파일:    demo.ctl
데이터 파일:    demo.ctl
  부적합한 파일:     demo.bad
  폐기 파일:    지정 사항 없음

 (모든 폐기된 레코드 허용)

로드할 건수: ALL
생략 건수:  0
허용 오류수:  50
바인드 배열:  64 행, 최대 256000 바이트
계속:    지정 사항 없음
사용된 경로:      규약

테이블 DEPT, 로드되었습니다 개개의 논리 레코드로부터
이 테이블에 적당한 Insert 옵션: TRUNCATE

   열 이름                        위치    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

월 12월 29 14:13:30 2014에 실행 개시
월 12월 29 14:13:30 2014에 실행 종료

경과 시간:        00:00:00.07
CPU 시간:         00:00:00.00

4. 테스트2(최대 길이 초과 오류)

[oracle@mydream test]$ cat demo2.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
TRUNCATE
FIELDS TERMINATED BY ','
(DEPTNO, DNAME, LOC )
BEGINDATA
10, sales, test
20, Accounting, test
30, Consulting, test
40, Finance, ****************************************************************************************************************************************************************************************************************************************************************


SQL*Loader: Release 11.2.0.1.0 - Production on 월 12월 29 14:18:42 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

제어 파일:    demo2.ctl
데이터 파일:    demo2.ctl
  부적합한 파일:     demo2.bad
  폐기 파일:    지정 사항 없음

 (모든 폐기된 레코드 허용)

로드할 건수: ALL
생략 건수:  0
허용 오류수:  50
바인드 배열:  64 행, 최대 256000 바이트
계속:    지정 사항 없음
사용된 경로:      규약

테이블 DEPT, 로드되었습니다 개개의 논리 레코드로부터
이 테이블에 적당한 Insert 옵션: TRUNCATE

   열 이름                        위치    Len   Term Encl 데이터유형
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *   ,       CHARACTER
DNAME                                NEXT     *   ,       CHARACTER
LOC                                  NEXT     *   ,       CHARACTER

레코드 4: 기각됨 - 테이블 DEPT, 열 LOC에 오류
데이터 파일의 필드가 최대 길이를 초과했습니다
테이블 DEPT:
  3 행 로드되었습니다.
  데이터 오류 때문에 1 행(이)가 로드되지 않았습니다
  모든 WHEN절이 실패하여 0 행(이)가 로드되지 않았습니다
  모든 필드가 NULL이어서 0 행(이)가 로드되지 않았습니다


바인드 배열에 할당된 영역:             49536바이트(64 행)
읽기 버퍼 바이트: 1048576

생략된 논리 레코드의 합계:         0
읽어낸 논리 레코드의 합계:         4
거부된 논리 레코드의 합계:         1
폐기된 논리 레코드의 합계:         0

월 12월 29 14:18:42 2014에 실행 개시
월 12월 29 14:18:42 2014에 실행 종료

경과 시간:        00:00:00.07
CPU 시간:         00:00:00.00



[oracle@mydream test]$ cat demo2_2.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
TRUNCATE
FIELDS TERMINATED BY ','
(DEPTNO, DNAME, LOC  char(1000) )
BEGINDATA
10, sales, test
20, Accounting, test
30, Consulting, test
40, Finance, ****************************************************************************************************************************************************************************************************************************************************************
[oracle@mydream test]$ sqlldr genie/genie control=demo2_2.ctl

SQL*Loader: Release 11.2.0.1.0 - Production on 월 12월 29 14:27:08 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

커밋 시점에 도달 - 논리 레코드 개수 4

[oracle@mydream test]$ cat demo2_2.log

SQL*Loader: Release 11.2.0.1.0 - Production on 월 12월 29 14:29:59 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

제어 파일:    demo2_2.ctl
데이터 파일:    demo2_2.ctl
  부적합한 파일:     demo2_2.bad
  폐기 파일:    지정 사항 없음

 (모든 폐기된 레코드 허용)

로드할 건수: ALL
생략 건수:  0
허용 오류수:  50
바인드 배열:  64 행, 최대 256000 바이트
계속:    지정 사항 없음
사용된 경로:      규약

테이블 DEPT, 로드되었습니다 개개의 논리 레코드로부터
이 테이블에 적당한 Insert 옵션: TRUNCATE

   열 이름                        위치    Len   Term Encl 데이터유형
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *   ,       CHARACTER
DNAME                                NEXT     *   ,       CHARACTER
LOC                                  NEXT  1000   ,       CHARACTER

테이블 DEPT:
  4 행 로드되었습니다.
  데이터 오류 때문에 0 행(이)가 로드되지 않았습니다
  모든 WHEN절이 실패하여 0 행(이)가 로드되지 않았습니다
  모든 필드가 NULL이어서 0 행(이)가 로드되지 않았습니다


바인드 배열에 할당된 영역:             97152바이트(64 행)
읽기 버퍼 바이트: 1048576

생략된 논리 레코드의 합계:         0
읽어낸 논리 레코드의 합계:         4
거부된 논리 레코드의 합계:         0
폐기된 논리 레코드의 합계:         0

월 12월 29 14:29:59 2014에 실행 개시
월 12월 29 14:29:59 2014에 실행 종료

경과 시간:        00:00:00.08
CPU 시간:         00:00:00.00

6. 테스트3(구분자를 포함하는 데이터 로드 방법 )


-- 콤마(,)를 구분자로 하고 문자열은 따옴표(")로 감싼다는 의미
[oracle@mydream test]$ cat demo3.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
TRUNCATE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO, DNAME, LOC )
BEGINDATA
10, sales, "test, TEST"
20, Accounting, "test, ""TEST"""
30, Consulting, test
40, Finance,  AAAAA

[oracle@mydream test]$ sqlldr genie/genie control=demo3.ctl

SQL*Loader: Release 11.2.0.1.0 - Production on 월 12월 29 17:15:38 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

커밋 시점에 도달 - 논리 레코드 개수 4

SQL > select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- --------------------
        10 sales          test, TEST
        20 Accounting     test, "TEST"
        30 Consulting     test
        40 Finance        AAAAA

-- 데이터 사이에 tab, 스페이스(공백), 개행문자 전까지 문자열을 파싱함
-- 10 : 탭 적용
-- 20 : 공백 2칸 적용
-- 30 : 공백 1칸 적용
[oracle@mydream test]$ cat demo4_2.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
TRUNCATE
FIELDS TERMINATED BY WHITESPACE
(DEPTNO, DNAME, LOC )
BEGINDATA
10      sales   TEST
20  business aaaa
30 aaa  bbb

SQL> /

    DEPTNO DNAME          LOC
---------- -------------- --------------------
        10 sales          TEST
        20 business       aaaa
        30 aaa            bbb


-- X'09'는 16진수 포멧을 사용한 탭문자(ASCII 9는 탭문자)
-- 10 : 탭 2번 적용
-- 20 : 탭 1번 적용
-- 30 : 공백 1칸 적용
[oracle@mydream test]$ cat demo5.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
TRUNCATE
FIELDS TERMINATED BY X'09'
(DEPTNO, DNAME, LOC )
BEGINDATA
10              sales           TEST
20      business        aaaa
30 aaa bbb


[oracle@mydream test]$ sqlldr genie/genie control=demo5.ctl

SQL*Loader: Release 11.2.0.1.0 - Production on 월 12월 29 17:33:23 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

커밋 시점에 도달 - 논리 레코드 개수 3


SQL> /

    DEPTNO DNAME          LOC
---------- -------------- --------------------
        10                sales                                <-- 탭 2번 적용
        20 business       aaaa                             <-- 탭 1번 적용


[oracle@mydream test]$ cat demo5.log

SQL*Loader: Release 11.2.0.1.0 - Production on 월 12월 29 17:39:35 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

제어 파일:    demo5.ctl
데이터 파일:    demo5.ctl
  부적합한 파일:     demo5.bad
  폐기 파일:    지정 사항 없음

 (모든 폐기된 레코드 허용)

로드할 건수: ALL
생략 건수:  0
허용 오류수:  50
바인드 배열:  64 행, 최대 256000 바이트
계속:    지정 사항 없음
사용된 경로:      규약

테이블 DEPT, 로드되었습니다 개개의 논리 레코드로부터
이 테이블에 적당한 Insert 옵션: TRUNCATE

   열 이름                        위치    Len   Term Encl 데이터유형
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *  WHT      CHARACTER
DNAME                                NEXT     *  WHT      CHARACTER
LOC                                  NEXT     *  WHT      CHARACTER

레코드 3: 기각됨 - 테이블 DEPT, 열 DNAME에 오류
논리 레코드가 종료하기 전에 열을 찾지 못했습니다 (TRAILING NULLCOLS 사용)
테이블 DEPT:
  2 행 로드되었습니다.
  데이터 오류 때문에 1 행(이)가 로드되지 않았습니다
  모든 WHEN절이 실패하여 0 행(이)가 로드되지 않았습니다
  모든 필드가 NULL이어서 0 행(이)가 로드되지 않았습니다


바인드 배열에 할당된 영역:             49536바이트(64 행)
읽기 버퍼 바이트: 1048576

생략된 논리 레코드의 합계:         0
읽어낸 논리 레코드의 합계:         3
거부된 논리 레코드의 합계:         1
폐기된 논리 레코드의 합계:         0

월 12월 29 17:39:35 2014에 실행 개시
월 12월 29 17:39:35 2014에 실행 종료

경과 시간:        00:00:00.06
CPU 시간:         00:00:00.00


-- filler라는 키워드를 사용해 컬럼매핑을 건너뛸 수 있다.
-- 아래 데이터는 탭을 2회 사용하였음(2번 컬럼, 4번 컬럼의 매핑을 건너뛰겠다는 의미)
[oracle@mydream test]$ cat demo6.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
TRUNCATE
FIELDS TERMINATED BY x'09'
(DEPTNO, dummy1 filler, DNAME, dummy2 filler, LOC )
BEGINDATA
10              sales           TEST

SQL> /

    DEPTNO DNAME          LOC
---------- -------------- --------------------
        10 sales          TEST

7. 테스트4( 고정길이 포맷 데이터 로드 방법)

[oracle@mydream test]$ cat demo7.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
(
    DEPTNO position(1:2),
    DNAME position(3:16),
    loc   position(17:29)
)
BEGINDATA
10Accounting    Virginia,Korea

SQL> /

    DEPTNO DNAME          LOC
---------- -------------- --------------------
        10 Accounting     Virginia,Kore      


-- position의 위치는 자유롭게 앞뒤로 움직일 수 있다.
-- entire_line 컬럼 참조
[oracle@mydream test]$ cat demo8.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
(
    DEPTNO position(1:2),
    DNAME position(3:16),
    loc   position(17:30),
    entire_line position(1:29)
)
BEGINDATA
10Accounting    Virginia,Korea

SQL> /

    DEPTNO DNAME          LOC                  ENTIRE_LINE
---------- -------------- -------------------- -----------------------------
        10 Accounting     Virginia,Korea       10Accounting    Virginia,Kore

-- *는 컨트롤 파일의 앞 필드가 끝난 위치를 기준으로 1바이트 추가된 지점을 자동으로 시작점으로 인식
[oracle@mydream test]$ cat demo9.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
(
    DEPTNO position(1:2),
    DNAME position(*:16),
    loc   position(*:30),
    entire_line position(1:29)
)
BEGINDATA
10Accounting    Virginia,Korea

SQL> /

    DEPTNO DNAME          LOC                  ENTIRE_LINE
---------- -------------- -------------------- -----------------------------
        10 Accounting     Virginia,Korea       10Accounting    Virginia,Kore

-- *+2 는 앞 시작점에서 2칸을 더 건너띄겠다는 의미
-- loc 앞 2자리가 짤렸음을 관찰
[oracle@mydream test]$ cat demo10.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
(
    DEPTNO position(1:2),
    DNAME position(*:16),
    loc   position(*+2:30),
    entire_line position(1:29)
)
BEGINDATA
10Accounting    Virginia,Korea

SQL> /

    DEPTNO DNAME          LOC                  ENTIRE_LINE
---------- -------------- -------------------- -----------------------------
        10 Accounting     rginia,Korea         10Accounting    Virginia,Kore


-- 컬럼의 길이를 직접 지정하여 사용할 수도 있음
[oracle@mydream test]$ cat demo11.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
(
    DEPTNO position(1) char(2),
    DNAME position(*) char(14),
    loc   position(*) char(14),
    entire_line position(1) char(29)
)
BEGINDATA
10Accounting    Virginia,Korea

SQL> /

    DEPTNO DNAME          LOC                  ENTIRE_LINE
---------- -------------- -------------------- -----------------------------
        10 Accounting     Virginia,Korea       10Accounting    Virginia,Kore

8. 테스트5(날짜 데이터 로드방법)


-- date 타입으로 필드를 지정함
[oracle@mydream test]$ cat demo12.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
( DEPTNO
, DNAME
, LOC
, LAST_UPDATED DATE 'dd/mm/yyyy'
)
BEGINDATA
10,Accounting,Virginia,1/5/2000
20,Sale,Virginia,21/6/1999


QL> /

    DEPTNO DNAME          LOC                            ENTIRE_LINE                   LAST_UPD
---------- -------------- ------------------------------ ----------------------------- --------
        10 Accounting     Virginia                                                     00/05/01
        20 Sale           Virginia                                                     99/06/21

9. 테스트7(함수를 사용한 데이터 로드 방법)


-- sql 엔진을 거쳐야 하기 때문에 direct path 방식으로는 처리할 수 없음
[oracle@mydream test]$ cat demo13.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
( DEPTNO
, DNAME       "upper(:dname)"
, LOC          "upper(:loc)"
, LAST_UPDATED DATE 'dd/mm/yyyy'
)
BEGINDATA
10,Accounting,Virginia,1/5/2000
20,Sale,Virginia,21/6/1999
30,Consulting,virginia,5/1/2000

SQL> /

    DEPTNO DNAME          LOC                            ENTIRE_LINE                   LAST_UPD
---------- -------------- ------------------------------ ----------------------------- --------
        10 ACCOUNTING     VIRGINIA                                                     00/05/01
        20 SALE           VIRGINIA                                                     99/06/21
        30 CONSULTING     VIRGINIA       


-- 입력할 데이터에 존재하지 않는 컬럼을 사용할 경우
[oracle@mydream test]$ cat demo14.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
( DEPTNO
, DNAME       "upper(:dname)"
, LOC          "upper(:loc)"
, LAST_UPDATED DATE 'dd/mm/yyyy'
, ENTIRE_LINE   ":deptno||:dname||:loc||:last_updated"
)
BEGINDATA
10,Accounting,Virginia,1/5/2000
20,Sale,Virginia,21/6/1999
30,Consulting,virginia,5/1/2000


[oracle@mydream test]$ cat demo14.log

SQL*Loader: Release 11.2.0.1.0 - Production on 월 12월 29 18:57:36 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

제어 파일:    demo14.ctl
데이터 파일:    demo14.ctl
  부적합한 파일:     demo14.bad
  폐기 파일:    지정 사항 없음

 (모든 폐기된 레코드 허용)

로드할 건수: ALL
생략 건수:  0
허용 오류수:  50
바인드 배열:  64 행, 최대 256000 바이트
계속:    지정 사항 없음
사용된 경로:      규약

테이블 DEPT, 로드되었습니다 개개의 논리 레코드로부터
이 테이블에 적당한 Insert 옵션: REPLACE

   열 이름                        위치    Len   Term Encl 데이터유형
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *   ,       CHARACTER
DNAME                                NEXT     *   ,       CHARACTER
    열에 대한 SQL 문자열 : "upper(:dname)"
LOC                                  NEXT     *   ,       CHARACTER
    열에 대한 SQL 문자열 : "upper(:loc)"
LAST_UPDATED                         NEXT     *   ,       DATE dd/mm/yyyy
ENTIRE_LINE                          NEXT     *   ,       CHARACTER
    열에 대한 SQL 문자열 : ":deptno||:dname||:loc||:last_updated"

레코드 1: 기각됨 - 테이블 DEPT, 열 ENTIRE_LINE에 오류
논리 레코드가 종료하기 전에 열을 찾지 못했습니다 (TRAILING NULLCOLS 사용)
레코드 2: 기각됨 - 테이블 DEPT, 열 ENTIRE_LINE에 오류
논리 레코드가 종료하기 전에 열을 찾지 못했습니다 (TRAILING NULLCOLS 사용)
레코드 3: 기각됨 - 테이블 DEPT, 열 ENTIRE_LINE에 오류
논리 레코드가 종료하기 전에 열을 찾지 못했습니다 (TRAILING NULLCOLS 사용)
테이블 DEPT:
  0 행 로드되었습니다.
  데이터 오류 때문에 3 행(이)가 로드되지 않았습니다
  모든 WHEN절이 실패하여 0 행(이)가 로드되지 않았습니다
  모든 필드가 NULL이어서 0 행(이)가 로드되지 않았습니다


바인드 배열에 할당된 영역:             82560바이트(64 행)
읽기 버퍼 바이트: 1048576

생략된 논리 레코드의 합계:         0
읽어낸 논리 레코드의 합계:         3
거부된 논리 레코드의 합계:         3
폐기된 논리 레코드의 합계:         0

월 12월 29 18:57:36 2014에 실행 개시
월 12월 29 18:57:36 2014에 실행 종료

경과 시간:        00:00:00.04
CPU 시간:         00:00:00.00

-- trailing nullcols 를 명시함
[oracle@mydream test]$ cat demo15.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
( DEPTNO
, DNAME       "upper(:dname)"
, LOC          "upper(:loc)"
, LAST_UPDATED DATE 'dd/mm/yyyy'
, ENTIRE_LINE   ":deptno||:dname||:loc||:last_updated"
)
BEGINDATA
10,Accounting,Virginia,1/5/2000
20,Sale,Virginia,21/6/1999
30,Consulting,virginia,5/1/2000

SQL> /

    DEPTNO DNAME          LOC                            ENTIRE_LINE                   LAST_UPD
---------- -------------- ------------------------------ ----------------------------- --------
        10 ACCOUNTING     VIRGINIA                       10AccountingVirginia1/5/2000  00/05/01
        20 SALE           VIRGINIA                       20SaleVirginia21/6/1999       99/06/21
        30 CONSULTING     VIRGINIA                       30Consultingvirginia5/1/2000  00/01/05

-- 날짜 형태 변경
SQL> alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS';

세션이 변경되었습니다.

-- sql loader는 insert문에 바인드 변수를 적용한 것과 동일하게 사용가능(아래와 같이 다양한 종류의 쿼리가 사용가능함)
[oracle@mydream test]$ cat demo16.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
( DEPTNO
, DNAME       "upper(:dname)"
, LOC          "upper(:loc)"
, LAST_UPDATED
    "case
        when length(:last_updated) > 9 then to_date(:last_updated, 'yyyy/mm/dd hh24:mi:ss')
        when instr(:last_updated, ':') > 0 then to_date(:last_updated, 'hh24:mi:ss')
        else to_date(:last_updated, 'yyyy/mm/dd')
     end"
)
BEGINDATA
10,Accounting,Virginia,2015/01/01 11:12:15
20,Sale,Virginia, 02:23:54
30,Consulting,virginia,2014/12/31 11:50:52
40,Finance,virginia,2013/12/22



SQL> SELECT * FROM DEPT;

    DEPTNO DNAME          LOC                            ENTIRE_LINE                   LAST_UPDATED
---------- -------------- ------------------------------ ----------------------------- -------------------
        10 ACCOUNTING     VIRGINIA                                                     2015/01/01 11:12:15
        20 SALE           VIRGINIA                                                     2014/12/01 02:23:54
        30 CONSULTING     VIRGINIA                                                     2014/12/31 11:50:52
        40 FINANCE        VIRGINIA                                                     2013/12/22 00:00:00


10. 테스트8( 개행문자를 포함한 데이터 로드 방법 )

개행문자 대신 다른 문자를 사용
SQL> alter table dept add comments varchar2(4000);

테이블이 변경되었습니다.

[oracle@mydream test]$ cat demo17.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
( DEPTNO
, DNAME       "upper(:dname)"
, LOC          "upper(:loc)"
, COMMENTS     "replace(:comments, '\\n', chr(10))"
)
BEGINDATA
10,Accounting,Virginia, "개행문자 테스트\n 중입니다."
20,Sale,Virginia, "개행문자 테스트\n 중입니다."
30,Consulting,virginia, "개행문자 테스트\n\n 중입니다."
40,Finance,virginia, "개행문자 테스트\n\n\n 중입니다."


SQL> /

    DEPTNO DNAME          LOC                  ENTIRE_LIN LAST_UPD COMMENTS
---------- -------------- -------------------- ---------- -------- ------------------------------
        10 ACCOUNTING     VIRGINIA                                  "개행문자 테스트
                                                                    중입니다."

        20 SALE           VIRGINIA                                  "개행문자 테스트
                                                                    중입니다."

        30 CONSULTING     VIRGINIA                                  "개행문자 테스트

                                                                    중입니다."

        40 FINANCE        VIRGINIA                                  "개행문자 테스트


                                                                    중입니다."


FIX 속성사용
[oracle@mydream test]$ cat demo18.ctl
LOAD DATA
INFILE demo18.dat "fix 80"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
( DEPTNO
, DNAME       "upper(:dname)"
, LOC          "upper(:loc)"
, COMMENTS
)

-- 데이터 파일
[oracle@mydream test]$ cat demo18.dat
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

[oracle@mydream test]$ od -c -w10 -v demo18.dat
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   2   0   ,   A
0000074   c   c   o   u   n   t   i   n   g   ,
0000106   v   i   g   i   n   i   a   ,   T   h
0000120   i   s       i   s       t   h   e
0000132   A   c   c   o   u   n   t   i   n   g
0000144   \   n   O   f   f   i   c   e       i
0000156   n       V   i   r   g   i   n   i   a
0000170  \n   3   0   ,   C   o   n   s   u   l
0000202   t   i   n   g   ,   v   i   g   i   n
0000214   i   a   ,   T   h   i   s       i   s
0000226       t   h   e       C   o   n   s   u
0000240   l   t   i   n   g   \   n   O   f   f
0000252   i   c   e       i   n       V   i   r
0000264   g   i   n   i   a  \n   4   0   ,   F
0000276   i   n   a   n   c   e   ,   v   i   g
0000310   i   n   i   a   ,   T   h   i   s
0000322   i   s       t   h   e       F   i   n
0000334   a   n   c   e   \   n   O   f   f   i
0000346   c   e       i   n       V   I   r   g
0000360   i   n   i   a  \n
0000365


-- 입력도중 실패
[oracle@mydream test]$ sqlldr genie/genie control=demo18.ctl

SQL*Loader: Release 11.2.0.1.0 - Production on 월 12월 29 22:06:32 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

커밋 시점에 도달 - 논리 레코드 개수 3
SQL*Loader-501: 파일 (demo18.dat)을 읽을 수 없습니다
SQL*Loader-566: 데이터 파일의 끝에서 부분 레코드 발견
SQL*Loader-2026: SQL Loader가 계속할 수 없으므로 로드가 중단되었습니다.

SQL> /

    DEPTNO DNAME          LOC                  ENTIRE_LIN LAST_UPD COMMENTS
---------- -------------- -------------------- ---------- -------- ------------------------------
        10 SALELS         VIGINIA                                  This is the sales\nOffice in v
                                                                   irginia
                                                                   20


– 문자열 끝에 공백이 존재하여 trim 함수 적용이 필요함

주의점

줄의 종결 표시는 플랫폼에 따라 다르다.

유닉스 : \n (SQL에서 CHR(10))
윈도우 : \r\n (SQL에서 CHR(13) || CHR(10))

FIX 방법을 사용한다면 반드시 같은 플랫폼에서 파일을 생성하고 로드해야만 한다

11. 테스트9( VAR 속성사용 )

[oracle@mydream test]$ cat demo19.ctl
LOAD DATA
INFILE demo19.dat "var 3"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
( DEPTNO
, DNAME       "upper(:dname)"
, LOC          "upper(:loc)"
, COMMENTS
)

-- 유닉스에서는 개행문자가 1byte, 윈도우에서는 2byte 이므로, 데이터를 생성하는 운영체제에 따라 크기를 다르게 해줘야 함
-- (윈도우에서는 각행을 056, 066, 066, 060 으로 변경해야 함)
[oracle@mydream test]$ cat demo19.dat
05510,Sales,Virginia,This is the Sales
Office in Virginia
06520,Accounting,viginia,This is the Accounting
Office in Virginia
06530,Consulting,viginia,This is the Consulting
Office in Virginia
05940,Finance,viginia,This is the Finance
Office in Virginia


-- 데이터의 길이 지정이 잘못될 경우 데이터 입력에 오류가 발생
[oracle@mydream test]$ sqlldr genie/genie control=demo19.ctl

SQL*Loader: Release 11.2.0.1.0 - Production on 월 12월 29 23:47:22 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

커밋 시점에 도달 - 논리 레코드 개수 3
SQL*Loader-501: 파일 (demo19.dat)을 읽을 수 없습니다
SQL*Loader-566: 데이터 파일의 끝에서 부분 레코드 발견
SQL*Loader-2026: SQL Loader가 계속할 수 없으므로 로드가 중단되었습니다.


-- 데이터가 3개행만 입력되었음
SQL> /

    DEPTNO DNAME          LOC             ENTIRE_LINE     LAST_UPD COMMENTS
---------- -------------- --------------- --------------- -------- ------------------------------
        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



12. 테스트10( str 속성 사용)


-- 파이프(|)를 문장의 끝으로 인식하고자 할 때
SQL> select utl_raw.cast_to_raw('|'||chr(10)) from dual;

UTL_RAW.CAST_TO_RAW('|'||CHR(10))
--------------------------------------------------------------------------------------------------------------------------------
7C0A

-- 위에서의 7C0A를 문자의 끝으로 인식하게 한다.
[oracle@mydream test]$ cat demo20.ctl
LOAD DATA
INFILE demo20.dat "str X'7C0A'"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
( DEPTNO
, DNAME       "upper(:dname)"
, LOC          "upper(:loc)"
, COMMENTS
)

[oracle@mydream test]$ cat demo20.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|

SQL> /

    DEPTNO DNAME          LOC             ENTIRE_LINE     LAST_UPD COMMENTS
---------- -------------- --------------- --------------- -------- ------------------------------
        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


참고

윈도우에서 생성한 파일의 경우는 아래처럼 사용함
SQL> select utl_raw.cast_to_raw('|'||chr(13)||chr(10)) str_raw from dual;

STR_RAW
----------
7C0D0A

13. 테스트11( PL/SQL로 LOB 데이터 로딩 )


--DBMS_LOB 패키지(LoadFromFile, LoadBLOBFromFile, LoadCLOBFromFile)를 이용

-- 디렉토리 생성
[oracle@mydream app]$ mkdir /app/demo_dir

-- 디렉토리 객체 생성
-- 대문자 디렉토리 생성
SQL> create or replace directory dir1 as '/app/demo_dir/';

디렉토리가 생성되었습니다.

-- 소문자 디렉토리 생성
SQL> create or replace directory "dir2" as '/app/demo_dir/';

디렉토리가 생성되었습니다.

-- 데모 테이블 생성
SQL> create table demo
  2  ( id   int primary key,
  3    theclob clob
  4  )
  5  /

테이블이 생성되었습니다.

-- 테스트 파일 생성
SQL> !echo 'Hello World!!' > /app/demo_dir/test.txt

-- 프로시저 생성
SQL> DECLARE
  2      L_CLOB    CLOB;
  3      L_BFILE   BFILE;
  4  BEGIN
  5      INSERT INTO DEMO VALUES (1, EMPTY_CLOB()) RETURNING THECLOB INTO L_CLOB;     -- CLOB을 EMPTY_CLOB()로 초기화
  6
  7
  8      L_BFILE    := BFILENAME('DIR1', 'test.txt');                                 -- BFIEL 객체 생성,   'dir2'의 경우에는 소문자로도 가능
  9      DBMS_LOB.FILEOPEN(L_BFILE);                                                  -- LOB를 OPEN
 10      DBMS_LOB.LOADFROMFILE(L_CLOB, L_BFILE, DBMS_LOB.GETLENGTH(L_BFILE));         -- LOB locator에 운영체제파일 /tmp/test.txt의 내용 로드

 11   12      DBMS_LOB.FILECLOSE(L_BFILE);                                                 -- BFILE CLOSE
 13  END;
 14  /

PL/SQL 처리가 정상적으로 완료되었습니다.


-- 글자가 깨져서 들어감. 원인은 못 밝힘 ^^;
-- export LANG=C로 해보았으나 한글, 영문 모두 깨짐
SQL> select * from demo;

        ID THECLOB
---------- --------------------------------------------------------------------------------
         3 ??漠????
         5 ??漠???℡?
         1 ??漠????
         2 ??漠????
         4 쟑뇛??뫆깈敬????℡

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.