SQL loader 사용하기

>> SQL*loader <<

  SQL*Loader는 외부 화일의 데이타를 ORACLE  데이타베이스의 테이블에 넣기 위한 유틸리티이다.  SQL*loader를 사용하려면 외부 데이타 화일과 콘트롤화일이 필요하다.    
  콘트롤화일이라고 하는 것은 로드하는 데이타의 정보를 저장한 화일인데 예를 들면 다음과 같다.

   load data          ->  콘트롤화일의 앞에는 반드시 필요함
   infile sample.dat    ->  외부 데이타 화일을 지정
   replace             ->  테이블에 데이타 넣는 방법 지정
   into table TABLE_NAME -> 데이타를 로드하는 테이블을 지정
   fields terminated by ','    ->  데이타 필드의 종결문자 지정
   (a integer external,b char) ->  테이블의 열 및 외부데이타
                                     화일의 데이타 형을 지정
                            
    *참고로 Replace 외에 다음의 옵션이 가능하다.

   replac   -> 테이블의 기존 행을 모두 삭제(delete)하고
                 Insert(7.0 에서는 truncate 함)
   append  -> 새로운 행을 기존의 데이타에 추가
   insert   -> 비어 있는 테이블에 넣을 때
   truncate -> 테이블의 기존 데이타를 모두 truncate 하고 인서트
               (7.0에는 없음)
  
  SQL*loader를 실행하면 아래의 화일이 생성된다.
  -  로드 작업의 결과와 에러 등을 기록한 로그화일(확장자는 log)  
  -  에러 때문에 로드가 안된 레코드를 저장한 화일(확장자는 bad)
  -  사용자의 선택 기준에 적합하지 않은 레코드를 저장한 파일
     (discard 화일)
      이것은 discardfile 옵션으로 별도로 지정해야 생성된다.
실행 방법

  $ sqlload scott/tiger control=sample.ctl data=sample.dat

  1. 임의의 컬럼에 문자열(값)을 입력한 경우

   [ 테이블 구조 ]
   create table cons_test
   (a number,  b number,    c number,    d varchar(10))

   [ 콘트롤 화일 ]
   load data
   infile cons.dat
   replace
   into table cons_test
   fields terminated by ','
   (a integer external,
    b integer external,
    c CONSTANT '100',
    d char)

   [ 외부 데이타 파일 cons.dat의 내용 ]
   1,2,DATA
   2,4,DATA2

   [ 검색결과 ]
   SQL> select * from cons_test;
       A       B       C    D
     ------- ----- ------ ------------
       1       2     100   DATA
       2       4     100   DATA2

  2. 로드한 때의 날짜를 넣고 싶은 경우

   [ 테이블 구조 ]
   create table sysdatetb
   (a number,    b date,    c varchar(10))

   [ 콘트롤 화일 ]
   load data
   infile sysdate2.dat
   replace
   into table sysdatetb
   fields terminated by ','
   (a integer external,
    b sysdate,
    c char(10))

   [ 외부 데이타 화일 ]
   111,STRINGS
   222,STRINGS2

   [ 검색결과 ]
   SQL> select * from sysdatetb;
        A         B           C
     -------  ---------  ----------
      111    13-MAY-94    STRING
      222    13-MAY-94    STRING2

SYSDATE는 Conventional Path의 경우는  실행시에 삽입된 각각의 레코드 배열마다, Direct Path의 경우는 로드된 각각의 레코드의 블록마다 사용된다. 새로운 값으로 변경된다.

 

  3. SEQUENCE를 임의의 수에서 임의의 수만큼 붙이고 싶은 경우

   [ 테이블 구조 ]
   create table seqtb
   (a varchar(10),    b number,    c varchar(10))

   [ 콘트롤 화일 ]
   load data
   infile seq.dat
   replace
   into table seqtb
   fields terminated by ','
   (a char,
    b sequence(100,5),
    c char)

   [ 외부 데이타 화일 ]
    1,a
    2,b
    3,c

   [ 검색결과 ]
   SQL> select * from seqtb;
     A    B     C
   ----- ---- --------
    1    100    a
    2    105    b
    3    110    c

 

  4. 논리 레코드를 구성하는 물리 레코드가 여러줄로 구성된 경우
     (물리 레코드의 1바이트째로 판단되는 경우)

  [ 테이블 구조 ]
  create table conti_test
  (a varchar(10),   b varchar(10),   c varchar(10))

  [ 콘트롤 화일 ]
  load data
  infile conti.dat
  replace
  continueif this  (1) = '%'
  into table conti_test
  fields terminated by ','
  (a char,
   b char,
   c char)

  [ 외부 데이타 화일 ]
  %1,
  %2,
  3
  %A,B
  ,C
  %a,b
  %c
  %d
  ,ef

 

 

  [ 검색결과 ]

  SQL> select * from conti_test;

  A      B       C
---- ------ --------
  1      2       3
  A      B       C
  a      bcd     ef

  이 예의 경우 1바이트 째가 계속 행의 체크를 위해서 사용되기 때문에 실제의 데이타를 1바이트 째부터 시작해서는 안된다.
위의 경우, 레코드의 선두 바이트가 '%'일 때 다음의 레코드가 연결된다.

 

  5. 외부데이타 화일의 물리 레코드가 복수 레코드로 구성된 경우
     (구성하는 물리 레코드 수가 모두 일정한 경우)

  [ 테이블 구조 ]
  create table con_test  
  (a varchar(10),   b varchar(10),   c varchar(10))

  [ 콘트롤 화일 ]
  load data
  infile conti.dat
  replace
  concatenate 2
  into table con_test
  fields terminated by ','
  (a char,   b char,   c char)

  [ 외부 데이타 화일 ]
  1,2,
  3
  a,b,
  c
  A,
  B,C

  [ 검색결과 ]
  SQL> select * from con_test;
  A       B       C
----- ------ -------
  1       2       3
  a       b       c
A       B      C

  6. 포지션 지정시 char형 전후의 블랭크도 로드하고 싶은 경우

   [ 테이블 구조 ]
   create table pretb
   (a varchar(10),    b varchar(10),    c varchar(10))

   [ 콘트롤 화일 ]
   load data
   infile pre.dat
   preserve blanks
   into table pretb
   (a position(01:05) char,
    b position(06:10) char,
    c position(11:20) char)

   [ 외부 데이타 화일 ]
    12 4  67890 ab def hi
       2  67890 ab def hi

   [ 검색결과 ]
   SQL> select * from pretb;
        A       B         C
     ------- ------- ------------
      12 4    67890   ab def hi
         2    67890   ab def hi

   SQL> select length(a), length(c) from pretb;
   LENGTH(A) LENGTH(C)
   --------- ----------
        5        10
5        10

  7. 데이타가 없는 경우 NULL 데이타를 넣고자 할 때

   [ 테이블 구조 ]
   create table tratb
   (a varchar(10),    b varchar(10),    c varchar(10))

   [ 콘트롤 화일 ]
   load data
   infile tra.dat
   into table tratb
   fields termintated by ','
   trailing nullcols
   (a char,
    b char,
    c char)

   [ 외부 데이타 화일 ]
    1,aa,
    2,bb,FF
    3,cc,

   [ 검색결과 ]
   SQL> select * from tratbl
   A       B       C
   ----- ----- -------
     1    aa
     2    bb      FF
     3    cc

  trailing nullcols를 사용하지 않으면 1 레코드째와 3 레코드째가 데이타 에러가 된다.

   8. CHAR형 필드가 BLANK로 채워져 있을 때 NULL을 삽입하고 싶은 경우

   [ 테이블 구조 ]
   create table nulltb
   (a varchar(10),    b varchar(10),    c varchar(10))

   [ 콘트롤 화일 ]
   load data
   infile null.dat
   replace
   into table nulltb
   fields terminated by ','
   (a char,
    b char,
    c char(10) nullif c = blanks)

   [ 외부 데이타 화일 ]
   aa,bb, ,
   11,22, ,
   99,88,AA
   00,00,BB

   [ 검색결과 ]
   SQL> select * from nulltb;
   A       B       C
  ----- ------ ------
  aa      bb
  11      22      
  99      88      AA    
  00      00      BB

9. POSITION 지정시 BLANK를 그대로 로드하고 싶은 경우

   [ 테이블 구조 ]
   create table nulltb2 (a varchar(10),    b varchar(10),    c date)

   [ 콘트롤 화일 ]
   load data
   infile null3.dat
   replace
   preserve blanks
   into table nulltb2
   (a position(1:2) char,
    b position(3:4) char nullif b = blanks,
    c position(5:13) date "YY/MM/DD")

   [ 외부 데이타 화일 ]
   998892/11/11
      94/12/12

   [ 검색결과 ]
   SQL>select * from nulltb2;
    A       B       C
   ----- ----- ---------------
   99      88      92/11/11
                   94/12/12

  SQL> select length(a), length(b) from nulltb2;
  LENGTH(A) LENGTH(B)
  --------- ----------
        2         2
        2

  10. BLANK가 들어가 있을 때 0을 입력하고 싶은 경우

    [ 테이블 구조 ]
    create table def2
    (a varchar(10),     b varchar(10),     c number)

   [ 콘트롤 화일 ]
   load data
   infile def2.dat
   replace
   into table def2
   fields terminated by ','
   (a char,
    b char,
    c integer external defaultif c = blanks)

   [ 외부 데이타 화일 ]
   11,11,123
   22,22, ,
   33,33, ,
   44,44, ,

   [ 검색결과 ]
   SQL> select * from deft;
   A       B        C
  ----- -------- -------
    11      11       123
    22      22         0
    33      33         0
    44      44         0

 

11. 데이타가 NULL일 때 NULL이라고 하는 문자열을 넣을 경우

    [ 테이블 구조 ]
    create table ifnulltb
    (a varchar(10),     b varchar(10),     c varchar(10))

    [ 콘트롤 화일 ]
    load data
    infile ifnull.dat
    replace
    into table ifnulltb
    fields terminated by ','
    (a char,
     b char "nvl(:b,'NULL')",
     c char)

    [ 외부 데이타 화일 ]
    1,2,3,
    A,,B
    a,b,c

    [ 검색결과 ]
    SQL> select * from ifnulltb;
    A       B       C
   ----- ------ --------------
    1       2       3
    A       NULL    B
    a       b       c

   NVL과 같은 SQL 함수는 DIRECT LOAD의 경우 SQL 인터페이스를 경유하지 않기 때문에 사용할 수 없다.

  12. 어떤 열을 모두 대문자(소문자)로 변환하여 로드하는 경우

   [ 테이블 구조 ]
   create table uptb
   (a varchar(10),
    b varchar(10))

   [ 콘트롤 화일 ]
   load data
   infile upper.dat
   replace
   into table uptb
   fields terminated by ','
   (a char "lower(:a)",
    b char "upper(:b)")

   [ 외부 데이타 화일 ]
   aBcDeFg,AbCdEf
   ccDD11,ffGG22

   [ 검색결과 ]
   SQL>select * from uptb;
    A         B      
  ------- -------------
  abcdefg   ABCDEF
  cdd11     FFGG22