[SQL] Oracle SQL*Loader 자동 스크립트(컨트롤파일 생성)

데이터를 이관하다 보면 여러 가지 방법이 있습니다. 그중에서 SQL*Loader를 이용해서 올리는 방법을 쓰기도 합니다. 요즘에는 툴이 좋아져서 데이터를 DB에 바로 적재 할 수 있습니다. 그렇지 않을 경우에는 약간 수작업을 통해서 데이터를 적재해야 해야 합니다. 그러한 반복 적인 작업을 줄이고자 스크립트를 만들어 봤습니다.

SQL*Loader 사용법 : 바로가기

SQL*Loader 기술문서 : 바로가기 

1. 자동 생성 PL/SQL

DECLARE
   V_OWNER          VARCHAR2(30);
   V_TABLE_NM       VARCHAR2(30);
   V_REPLACE_CHECK  NUMBER(2);
BEGIN
   
  V_OWNER           := 'ESTENPARK';      --  스키마 명
  V_TABLE_NM        := 'ISTD001NT';   --  TABLE 명
  V_REPLACE_CHECK   := 2;             --  1 : TRUNCATE(삭제 후 삽입), 2 : APPEND(기존 데이터 이후 추가삽입)   
   

  DECLARE CURSOR C IS
    -- 오라클을 이용하여 SQL*Loader 문법에 해당하는 쿼리를 작성
    SELECT A.OWNER
          ,A.TABLE_NAME
          ,A.COLUMN_NAME
          ,CASE 
              WHEN  A.DATA_TYPE = 'VARCHAR2' 
                OR  A.DATA_TYPE = 'CHAR'      THEN  'CHAR'
              WHEN  A.DATA_TYPE = 'NUMBER'    THEN  'DECIMAL EXTERNAL'
              WHEN  A.DATA_TYPE = 'DATE'      THEN  'DATE "YYYY/MM/DD HH:MI:SS"'
              ELSE  NULL
           END AS DATA_TYPE
          ,A.COLUMN_ID AS COLUMN_ID
          ,LAST_VALUE(A.COLUMN_ID) OVER(PARTITION BY A.TABLE_NAME) LAST_COLUMN_ID
    FROM   DBA_TAB_COLUMNS A
    WHERE  A.OWNER      = V_OWNER
    AND    A.TABLE_NAME = V_TABLE_NM
    ORDER  BY A.COLUMN_ID;
   
  BEGIN

    -- SQL*Loader  Control File 문장 시작
  
    DBMS_OUTPUT.PUT_LINE('LOAD DATA');
    DBMS_OUTPUT.PUT_LINE('INFILE '''      || V_TABLE_NM || '.dat''');
    DBMS_OUTPUT.PUT_LINE('BADFILE '''     || V_TABLE_NM || '.bad''');
    DBMS_OUTPUT.PUT_LINE('DISCARDFILE ''' || V_TABLE_NM || '.dsc''');
    
    -- SQL*Loader  Control File 형식에 해당하는 REPLACE 옵션에 따라서 문장 변경
    IF  V_REPLACE_CHECK = 1 THEN 
      DBMS_OUTPUT.PUT_LINE('TRUNCATE INTO TABLE ' || V_OWNER || '.' || V_TABLE_NM );
    ELSE
      DBMS_OUTPUT.PUT_LINE('APPEND   INTO TABLE ' || V_OWNER || '.' || V_TABLE_NM );
    END IF;
    DBMS_OUTPUT.PUT_LINE('FIELDS TERMINATED BY "|"'); 
    DBMS_OUTPUT.PUT_LINE('TRAILING NULLCOLS (');
   
     FOR CTL_LIST IN C LOOP
       -- SQL*Loader  Control File 형식에 해당하는 콤마(,)를 마지막에는 제거 함.
       IF CTL_LIST.COLUMN_ID <> CTL_LIST.LAST_COLUMN_ID THEN 
         DBMS_OUTPUT.PUT_LINE( '    ' || CTL_LIST.COLUMN_NAME || '    ' || CTL_LIST.DATA_TYPE || ',' );
       ELSE
         DBMS_OUTPUT.PUT_LINE( '    ' || CTL_LIST.COLUMN_NAME || '    ' || CTL_LIST.DATA_TYPE );
       END IF;         

     END LOOP;
     DBMS_OUTPUT.PUT_LINE(')');
  END;  


EXCEPTION
   WHEN NO_DATA_FOUND THEN
      NULL;
   WHEN OTHERS THEN
      NULL;
END;