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

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

 

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

 

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

 

PL/SQL 쿼리

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83

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;