③ DBMS_METADATA

SQL Developer, Toad, 오렌지, PL/SQL Developer 같은 툴을 사용하면 오라클에 있는 객체에 대한 정보를 쉽게 볼 수 있다. 예컨대, SQL Developer 상에서 테이블을 검색해 해당 테이블의 생성 스크립트를 곧바로 추출해 낼 수 있는데 DBMS_METADATA 패키지를 사용해도 테이블을 비롯한 패키지, 프로시저 등의 객체 생성 스크립트를 뽑아낼 수 있다. 이 패키지에서 제공하는 함수와 프로시저는 총 13개나 되므로, 여기에서는 테이블 생성 스크립트를 추출해 내는 방법만 알아 보자. 이를 위해서는 GET_DDL이란 함수를 사용한다.

    DBMS_METADATA.GET_DDL(object_type IN VARCHAR2,
                          name IN VARCHAR2,
                          schema IN VARCHAR2 DEFAULT NULL,
                          version IN VARCHAR2 DEFAULT 'COMPATIBLE',
                          model IN VARCHAR2 DEFAULT 'ORACLE',
                          transform IN VARCHAR2 DEFAULT 'DDL')
            RETURN CLOB;

위 함수의 반환 타입은 CLOB로 LOB(Large Object)의 유형으로 이에 대해서는 다음 장에서 자세히 다룰 테니, 여기서는 이런 타입이 있다는 정도로만 알고 넘어가자. 사원 테이블에 대해 위 함수를 사용해 보자.

입력

    SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES', 'ORA_USER')
      FROM DUAL;

결과

      CREATE TABLE "ORA_USER"."EMPLOYEES"
       ( "EMPLOYEE_ID" NUMBER(6,0) NOT NULL ENABLE,
        "EMP_NAME" VARCHAR2(80) NOT NULL ENABLE,
        "EMAIL" VARCHAR2(50),
        "PHONE_NUMBER" VARCHAR2(30),
        "HIRE_DATE" DATE NOT NULL ENABLE,
        "SALARY" NUMBER(8,2),
        "MANAGER_ID" NUMBER(6,0),
        "COMMISSION_PCT" NUMBER(2,2),
        "RETIRE_DATE" DATE,
        "DEPARTMENT_ID" NUMBER(6,0),
        "JOB_ID" VARCHAR2(10),
        "CREATE_DATE" DATE DEFAULT SYSDATE,
        "UPDATE_DATE" DATE DEFAULT SYSDATE,
        CONSTRAINT "PK_EMPLOYEES" PRIMARY KEY ("EMPLOYEE_ID")
      USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
    DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "MYTS" ENABLE
      ) SEGMENT CREATION IMMEDIATE
      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
    DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "MYTS"

이번에는 직전에 사용했던 ch12_overload_pkg 패키지의 스크립트를 뽑아내 보자.

입력

    SELECT DBMS_METADATA.GET_DDL('PACKAGE', 'ch12_OVERLOAD_PKG', 'ORA_USER')
      FROM DUAL;

결과

      CREATE OR REPLACE PACKAGE "ORA_USER"."CH12_OVERLOAD_PKG" IS
      -- 매개변수로 사번을 받아 해당 사원의 부서명을 출력
      PROCEDURE fn_get_dep_nm( p_emp_id IN NUMBER);

      -- 매개변수로 사원명을 받아 해당 사원의 부서명을 출력
      PROCEDURE fn_get_dep_nm( p_emp_name IN VARCHAR2);

    END ch12_overload_pkg;

    CREATE OR REPLACE PACKAGE BODY "ORA_USER"."CH12_OVERLOAD_PKG" IS
    -- 매개변수로 사번을 받아 해당 사원의 부서명을 출력
      PROCEDURE fn_get_dep_nm( p_emp_id IN NUMBER)
      IS
          -- 부서명 변수
    vs_dep_nmdepartments.department_name%TYPE;
      BEGIN
        SELECT b.department_name
          INTO vs_dep_nm
          FROM employees a, departments b
         WHERE a.employee_id = p_emp_id
           AND a.department_id = b.department_id;

        DBMS_OUTPUT.PUT_LINE('emp_id: ' || p_emp_id || ' - ' || vs_dep_nm);

      END fn_get_dep_nm;

      -- 매개변수로 사원명을 받아 해당 사원의 부서명을 출력
      PROCEDURE fn_get_dep_nm( p_emp_name IN VARCHAR2)
    IS
        -- 부서명 변수
    vs_dep_nmdepartments.department_name%TYPE;
      BEGIN
        SELECT b.department_name
          INTO vs_dep_nm
          FROM employees a, departments b
         WHERE a.emp_name = p_emp_name
           AND a.department_id = b.department_id;

        DBMS_OUTPUT.PUT_LINE('emp_name: ' || p_emp_name || ' - ' || vs_dep_nm);

      END fn_get_dep_nm;
    END ch12_overload_pkg;

테이블이나 패키지 뿐만 아니라 일반 프로시저나 함수 등, 다른 객체의 생성 스크립트도 위와 같은 방법으로 모두 추출해 낼 수 있다.