[SQL] 오라클(Oracle) External Table Auto Script(자동 생성기)

보통 인터페이스에서 수신받은 데이터를 일괄적으로 테이블에 쌓는 작업을 많이 하게 되는데 그때 필요한 작업이 SQL Loader, 자바, C에서 로딩하는 여러 가지 방식이 있을 수 있습니다. 그러나 소스코딩이 많아서 업로딩 할 때 시간이 많이 들기 때문에 Oracle에서 추천하는 External Table을 이용하여 파일을 빠르게 로딩할 수 있습니다. 대량의 데이터를 업로딩 해보지 않았지만 500MB 파일을 로딩하는 데 큰 문제는 없었습니다.

 

다만, External Table -> 일반 적재 테이블 -> 서비스될 테이블 순으로 변경 절차가 많아진다는 단점은 보입니다. 그러나 DB 설계 시 표준화 작업을 제대로 했다면 위와 같은 일괄작업을 손쉽게 할 수 있습니다.

 

그럼 External Table(익스터널 테이블) 자동 생성 PL/SQL을 보여드리겠습니다.

 

PL/SQL 쿼리

 

DECLARE
   V_OWNER          VARCHAR2(30);
   V_TABLE_NM       VARCHAR2(30);
   V_DIC_DRY        VARCHAR2(50);
   V_FILE_EXEC      VARCHAR2(30);
   V_TABLE_GB       VARCHAR2(1);
BEGIN
   
  V_OWNER           := 'TESTDBA';         -- 스키마 명
  V_TABLE_GB        := 'T';               -- T : 하나의 테이블, A : 모든 테이블
  V_TABLE_NM        := 'FODTS002NT';      -- TABLE 명    
  V_DIC_DRY         := 'EXT_DIR';         -- 로컬 디렉토리 명
  V_FILE_EXEC       := 'dat';             -- 파일 확장자 명
  
  DECLARE CURSOR C IS
    -- 오라클을 이용하여 External Table 생성 초기 쿼리를 만든다.
    -- V_TABLE_GB = 'A' 는 사용하지 말 것.
    SELECT  
             A.OWNER
            ,A.TABLE_NAME
            ,A.COLUMN_NAME
            ,A.DATA_TYPE
            ,A.COLUMN_ID
            ,A.LAST_COLUMN_ID
    FROM     (
             SELECT 
                    A.OWNER
                   ,A.TABLE_NAME
                   ,A.COLUMN_NAME
                   ,DECODE(A.DATA_TYPE, 'VARCHAR2', A.DATA_TYPE||'('||A.DATA_LENGTH||')', 
                                        'NUMBER'  , A.DATA_TYPE||'('||A.DATA_PRECISION||', '||A.DATA_SCALE||')' ) AS DATA_TYPE
                   ,A.COLUMN_ID AS COLUMN_ID
                   ,LAST_VALUE(A.COLUMN_ID) OVER (PARTITION BY A.TABLE_NAME ORDER BY A.COLUMN_ID 
                                                  RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LAST_COLUMN_ID -- 테이블 컬럼ID 중에서 최고 값
             FROM   DBA_TAB_COLUMNS A
             WHERE  A.OWNER      = V_OWNER
             AND    (V_TABLE_GB = 'T' AND A.TABLE_NAME  = V_TABLE_NM      /* 특정 테이블에 대해서 만들 경우 */
                     OR
                     V_TABLE_GB = 'A' AND A.TABLE_NAME IN (SELECT    S.TABLE_NAME /* 유저에 있는 모든 테이블을 기준으로 생성 */
                                                            FROM     DBA_TABLES S
                                                            WHERE    S.OWNER = V_OWNER)              
                     )
             AND    A.COLUMN_NAME NOT IN ('ALTR_PSN_ID', 'ALTR_DTTM')
             ) A
    ORDER  BY A.TABLE_NAME, A.COLUMN_ID ;
   
  BEGIN
    -- Oracle External Table Script 시작
    DBMS_OUTPUT.PUT_LINE('CREATE TABLE '|| V_OWNER ||'.EXT_'||V_TABLE_NM );
    DBMS_OUTPUT.PUT_LINE('(');
     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(')                                                    ');
    DBMS_OUTPUT.PUT_LINE('ORGANIZATION EXTERNAL');
    DBMS_OUTPUT.PUT_LINE('  (  TYPE ORACLE_LOADER');
    DBMS_OUTPUT.PUT_LINE('     DEFAULT DIRECTORY '||V_DIC_DRY);
    DBMS_OUTPUT.PUT_LINE('     ACCESS PARAMETERS');
    DBMS_OUTPUT.PUT_LINE('       ( RECORDS DELIMITED BY NEWLINE');
    DBMS_OUTPUT.PUT_LINE('         NOBADFILE');
    DBMS_OUTPUT.PUT_LINE('         NODISCARDFILE');
    DBMS_OUTPUT.PUT_LINE('         NOLOGFILE');
    DBMS_OUTPUT.PUT_LINE('         FIELDS TERMINATED BY '||''''||'|'||'''');
    DBMS_OUTPUT.PUT_LINE('         MISSING FIELD VALUES ARE NULL     )');
    DBMS_OUTPUT.PUT_LINE('     LOCATION ('||V_DIC_DRY||':'||''''||V_TABLE_NM||'.'||V_FILE_EXEC||''')');    
    DBMS_OUTPUT.PUT_LINE('  )');
    DBMS_OUTPUT.PUT_LINE('REJECT LIMIT UNLIMITED');
    DBMS_OUTPUT.PUT_LINE('NOPARALLEL');
    DBMS_OUTPUT.PUT_LINE('NOMONITORING;');
  END; 

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      NULL;
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('ERROR');
      NULL;
END;