SQL*Loader part.2 고급

====================================================================================
 ORACLE 8i SQL*LOADER DATAFILE의 특정 FIELD DATA를 SKIP하고 LOADING하는 방법
====================================================================================

 아래의 예제와 같이 가변 길이의 filed들이 ',', '|' 와 같은 구분자로
 구분이 되고 있는 경우 oracle 8i부터 제공되는 'FILLER'라고 하는 필드
 구분자를 사용하여 상태인식자로 표시하여 insert시 skip할 수 있다.

 

    <Example>
    TABLE : skiptab
    ===========================
     col1 varchar2(20)
     col2 varchar2(20)
     col3 varchar2(20)

    CONTROLFIEL : skip.ctl
    LOAD DATA
    INFILE skip.dat
    INTO TABLE skiptab
    FIELDS TERMINATED BY ","
    (col1 char,
    col2 filler char,
    col3 char)

    DATAFILE : skip.dat
    SMITH,      DALLAS,        RESEARCH
    ALLEN,      CHICAGO,       SALES
    WARD,       CHICAGO,       SALES

    data loading :
    $sqlldr scott/tiger control=skip.ctl

    결과  :

    COL1          COL3
    -------------------------------------------------------
    SMITH         RESEARCH
    ALLEN         SALES
    WARD          SALES

 

 

 

======================================================
 또다른 예 readme.ctl
 SQL*LOADER를 이용하여 TEXT FILE을 INSERT하는 방법
======================================================

 

 SQL*LOADER를 이용하여 TEXT FILE을 그대로 DATABASE에 넣는 방법은 다음과 같다.
 여기서는 README.DAT 화일을 DATABASE의 README 테이블에 넣는 경우를 예로 들기로
 한다.

   <테이블 구조 : README>
     NAME   NULL  TYPE
     ----------------------
     NAME         VARCHAR2(10)
     TEXT         LONG

 

    <README.CTL>
     LOAD DATA
     INFILE readme.dat "fix 65535"
     APPEND
     CONCATENATE 10
     PRESERVE blanks
     INTO TABLE readme
     (name position(1:10) char,
      text char(65535))

     <README.DAT>
     원래의  README.DAT 앞에 10자리로 NAME 컬럼 내용을 추가하였음

     README     화일 내용

     <실행 명령>

   sqlldr username/password control=readme.ctl data=readme.dat bindsize=300000

  이렇게 하면 README.DAT의 내용이 하나의 RECORD로 되어 DATABASE에 입력된다.
  단, 최대로 입력 가능한 TEXT FILE의 크기는 200KB 이다.

 

 

================================================================
 SQL*LOADER에서 SEQUENCE 함수와 DECODE 함수 사용하는 방법
================================================================

   PURPOSE
   ---------

   Unique한 DATA을 load하고자 할때 쓰이는 SEQUENCE () 함수와 DECODE 함수의
   사용에 대해 알아보고자 한다.

   Explanation
   -----------
   table의 data에 unique한 값을 넣기 위해 sequence을 만들어 사용한다. 이것은
   블루틴 #10863 을 참고하면 알수 있다. 이때 loader에서 database의 sequence기능이
   아닌 sequence() 함수를 사용하는 것을 테스트해 보고자 한다.
   또한 decode 함수를 사용하는 내용을 여기서 다루고자 한다.

   함수를 사용하는 경우는 conventional path load인 경우에만 가능하며 direct path load
   인 경우는 적용되지 않음을 주의하자.
   함수를  사용할때 참조하고자 하는 컬럼앞에 콜론(:)을 붙이면 된다.

   SEQUENCE 를 사용하면 각각의 레코드를 로드할때 특정 컬럼을 증가할수 있다.
   단 무시되거나 잘못되어 들어가지 않은 레코드에 대해서는 증가을 하지 않기
   때문에 주의하여야 한다.

   SEQUENCE 함수의 옵션에 대해 알아보자.

    SEQUENCE(n,increment) - 지정한 n 값부터 시작하여 increment 값만큼 증가한다.

    SEQUENCE(COUNT,increment) - table에 이미 존재하는 로우들을 count한 수에서
                                시작하여 increment 값만큼 증가한다.

    SEQUENCE(MAX, increment - 해당 컬럼의 maximum 값에서 시작하여 increment
                              값만큼 증가한다.

    Examples
    ----------

    < Sequence 함수 사용 예 >

    1. sample table 생성

    create table t1
    (field1 number, field2 number, field3 varchar2(10));

 

    2. controlfile생성

    <sequence1.ctl>
    load data
    infile 'data1.dat'
    into table t1
    fields terminated by "," optionally enclosed by '"'
    (field1 SEQUENCE(MAX,1),
     field2,
     field3 )

 

    3. load할 datafile생성

    <data1.dat>

    1234, "ABC"
    3456, "CDF"

 

    4. 실행해 본다.

    $ sqlldr scott/tiger sequence1.ctl

    SQL> select * from t1;

        FIELD1     FIELD2 FIELD3
    ---------- ---------- --------------------
             1       1234 ABC
             2       3456 CDF

 

    < Decode 함수 사용 예 >

    test1 column이 'hello'이면 'goodbye'을 아니면 test1 column값을 로드하고자 한다.

    1. sample table 생성

    create table testldr
    (test1 varchar2(10), test2 varchar2(10));

 

    2. controlfile생성

    <decode1.ctl >

    Load data
    infile 'data2.dat'
    into table testldr
    fields terminated by ',' optionally enclosed by '"'
    (test1,
     test2 "decode(:test1, 'hello', 'goodbye', :test1)")

 

    3. load할 datafile생성

    <data2.dat>
    hello,""
    goodbye,""
    hey,""
    hello,""

 

    4. 실행해 본다.

    $ sqlldr scott/tiger decode1.ctl

    SQL> select * from testldr;

    SQL> select * from testldr;

    TEST1                TEST2
    -------------------- --------------------
    hello                goodbye
    goodbye              goodbye
    hey                  hey
    hello                goodbye

 

 

 

==================================================================
 SQL*LOADER에서 | (PIPE LINE)을 RECORD SEPARATOR로 사용하기
==================================================================

 

    PURPOSE
    -------

     Oracle8i부터는 , SQL*Loader을 사용할때 record terminator을 지정할 수 있게 되었다.

 

    Explanation
    -----------

     Oracle8i 이전에는 record seperator로 default로 linefeed(carriage return,
    newline 등)였다.  이전에는 VAR 또는 FIX 등의 적당한 file을 다루기 위한 옵션을
    주어야 하기 때문에 복잡한 감이 있었고 flexible하지 못했다.

     Oracle8i부터는 , SQL*Loader을 사용할때 record terminator을 지정할 수 있게
    되었다. newline 또는 carriage return 문자를 포함하는 data 또는 special 문자를
    포함하는 data를 load하고자 할때 record terminator를 hexadecimal로 지정하여 활용할 수 있다.

 

    Example
    --------

     다음의 예제는  '|' (pipe line)을 record separator로 사용한다.
     record separator를 사용하기 위해서 SQL*Loader의 control file에  'infile'절에 적당한 값을

     지정 하여야 한다.
     아래의 예는 '|' (pipe line)을 사용하기 위해서
     "str X'7c0a'"을 'infile'절에 지정하였다.

    --controlfile : test.ctl

    load data
    infile 'test.dat' "str X'7c0a'"
    into table test
    fields terminated by ',' optionally enclosed by '"'
    (col1, col2)

    --datafile:  test.dat

    1,this is the first line of the first record
    this is the second|
    2,this is the first line of the second record
    this is the second|

    SQL> desc test
     Name                                      Null?    Type
     ----------------------------------------- -------- -----------
     COL1                                               VARCHAR2(4)
     COL2                                               VARCHAR2(100)

    $  sqlldr scott/tiger control=test.ctl log=test.log

    load된 data을 보면 아래와 같이 carriage return이 들어가 있는 data가 한 column에
    제대로 들어간 것을 볼 수 있다.

    SQL> select * from test;

    COL1
    ----
    COL2
    --------------------------------------------------------------------------------
    1
    this is the first line of the first record
    this is the second

    2
    this is the first line of the second record
    this is the second

 

 

 

===========================================================================
특별한 값(SEQUENCE, 십진수, USERNAME)을 갖는 COLUMN을 LOADER로 넣기
===========================================================================

    PURPOSE
    ---------
    특별한 값(SEQUENCE, 십진수, USERNAME)을 갖는 COLUMN을 SQL*LOADER를
    이용하여 INSERT하는 방법에 대해 알아보도록 한다.

    Explanation
    -----------

    1.연산이 포함된 십진수의 load.

    은행의 경우 data가 transaction type, 계좌번호 거래액으로 될 경우,
    마지막 거래액은 함축적 의미를 가진 숫자값이다.
    아래의 예에서 실제 값은 100.73, 75.25, 820.00이다.

     DEPOSIT  10015 10073
     DEPOSIT  10012 7525
     WITHDRAWAL 10015 82000

    이것을 아래의 table로 load하려 한다.

    CREATE TABLE register
     (tx_type CHAR(15),
      acct    NUMBER,
      amt     NUMBER);

    cotrol file
     LOAD DATA
     INFILE 'month.dat'
     INTO TABLE register
     (tx_type POSITION(1:10),
     acct POSITION(13:17),
     amt POSITION(20:24) ":amt/100")

    이것을 load하면 sql*loader는 아래와 같은 insert 문을 만든다.

    INSERT INTO register (tx_type,acct,amt)
       VALUES (:data1, :data2, ":amt/100");

    그러므로 매 amt값마다 100으로 나눈 값을 얻을 수 있다.
    그러나 이 방법은 direct path 방식에서는 사용할 수 없다.

    2. database sequence를 load하기

    [예제1]

    먼저 좀 더 쉬운 방법인 position에 의해 구분되는 data file의 경우를 보자.

     - table
    CREATE TABLE load_db_seq_positional
     (seq_number NUMBER,
      data1  NUMBER,
      data2  CHAR(15);

     - sequence
    CREATE SEQUENCE db_seq
     START WITH 1
     INCREMENT BY ;
     - control file
     LOAD DATA
     INFILE *
     INTO TABLE load_db_seq_positional
     (seq_number "db_seq.nextval",
      data1  POSITION(1:5),
      data2   POSITION(6:15)
     )
     BEGINDATA
     11111AAAAAAAAAA
     22222BBBBBBBBBB

     - 결과
    select * from load_db_seq_positional;

    SEQ_NUMBER DATA1 DATA2
    ----------      -------  -----------
             1 11111  AAAAAAAAAA
             2 22222  BBBBBBBBBB

    [예제2]

    이번은 comma로 구분되는 data file이다. 여기에서 key는 file이 구분되어
    있다는 것이다.

    sql*loader는 data file에서 seq_number field값을 찾으려 할 것이다. 이
    경우 마지막 field인 seq_number값을 넣어 주어야 하는데 그 값이 없을 경우
    TRAILING NULLCOLS를 이용하여 loader에게 지정해 주어야 한다.
    trailing columns는 null or non_existent이다.

    위의 예제와 이름만 다른 (load_db_seq_delimited) table과 같은 sequence를
    사용하여 다음을 실행한다.
     - control file
     LOAD DATA
     INFILE *
     INTO TABLE load_db_seq_delimited
     FIELDS TERMINATED BY ","
     TRAILING NULLCOLS
     (data1,
      data2,
      seq_number "db_seq.nextval"
     )
     BEGINDATA
     11111,AAAAAAAAAA
     22222,BBBBBBBBBB
     - 결과
    select * from load_db_seq_delimited;

    SEQ_NUMBER DATA1 DATA2
    ----------    -------  ------
             3 11111  AAAAAAAAAA
             4 22222  BBBBBBBBBB

    sql*loader에서 위의 경우 아래와 같은 insert문을 만든다.

    INSERT INTO load_db_seq_delimiter (data1,data2,seq_number)
      VALUES (:data1,data2,"db_seq.nextval");

    위의 두방법 역시 direct path에서는 사용하지 못한다.

 

    3. sql*loader 실행 시 username을 load 하기

 

    [예제1]

    data file에서 field가 position으로 구분되어 지는 경우로 조금 쉽다.
    다음 방법은 delimiter로 구분된 것으로 좀 더 신경을 써야 한다.
    두 경우 모두 "USER" pseudo-variable을 사용하는데 만약 user ID를
    사용하려면 "UID"를 대신 사용한다.

     - table
    CREATE TABLE load_user_positional
     (username CHAR(30),
      data1  NUMBER,
      data2  CHAR(15);
     - control file
     LOAD DATA
     INFILE *
     INTO TABLE load_user_positional
     (username "USER",
      data1  POSITION(1:5),
      data2   POSITION(6:15)
     )
     BEGINDATA
     11111AAAAAAAAAA
     22222BBBBBBBBBB
     - 실행
     sqlldr scott/tiger load_user_d.ctl
     - 결과
    select * from load_user_positional;

      USERNAME  DATA1     DATA2
    ----------      -------   ----------
         SCOTT      11111       AAAAAAAAAA
         SCOTT      22222       BBBBBBBBBB

 

    [예제2]
    이 경우는 comma delimiter로 구분된 경우이다. 이경우 sql*loader는
    USERNAME field를 찾을 것이다.
    그러나 그 값이 없으므로 USERNAME FIELD는 CONTROL FILE 제일 뒤에 둔다.
    그리고 "TRAILING NULLCOLS"절로 뒤의 null또는 존재하지 않는 값을
    trailing columns라는 것을 나타내 준다.

     - table
    CREATE TABLE load_user_delimited
     (username CHAR(30),
      data1  NUMBER,
      data2  CHAR(15);
     - control file
     LOAD DATA
     INFILE *
     INTO TABLE load_user_delimited
     FIELD TERMINATED BY ","
     TRAILING NULLCOLS
     (data1,
      data2,
      username "USER"
     )
     BEGINDATA
     11111,AAAAAAAAAA
     22222,BBBBBBBBBB
     - 실행
     sqlldr jack/jack load_user_d.ctl

     - 결과
    select * from load_user_delimited;

      USERNAME   DATA1  DATA2
    ----------      -------  ----------
      JACK            11111    AAAAAAAAAA
      JACK            22222    BBBBBBBBBB

    이 loader의 작업을 insert문으로 만들면

    INSERT INTO load_user_delimiter(data1,data2,username)
       VALUES(:data1,:data2,USER);

    이 방법 역시 direct mode에서는 사용할 수 없다.