DBMS_UTILITY.EXPAND_SQL_TEXT 프로시저

12.1 버전에서 추가된 DBMS_UTILITY.EXPAND_SQL_TEXT 프로시저를 살펴보자.

테스트를 위해 아래와 같이 뷰를 생성하자.

-- 1
CREATE OR REPLACE VIEW emp_v AS SELECT * FROM emp WHERE deptno = 10;

 

아래와 DBMS_UTILITY.EXPAND_SQL_TEXT 프로시저를 수행할 수 있다. 2-2는 2-1의 결과를 포맷팅한 결과다. emp_v 뷰가 서브 쿼리로 변환된 것을 확인할 수 있다. SELECT *가 테이블 앨리어스가 포함된 칼럼으로 표시된 점도 흥미롭다.  

SET LONG 32767
VAR v_sql_text CLOB

-- 2-1
EXEC DBMS_UTILITY.EXPAND_SQL_TEXT (input_sql_text => 'SELECT * FROM emp_v', output_sql_text => :v_sql_text);

PRINT v_sql_text

V_SQL_TEXT
--------------------------------------------------------------------------------
SELECT "A1"."EMPNO" "EMPNO","A1"."ENAME" "ENAME","A1"."JOB" "JOB","A1"."MGR" "MG
R","A1"."HIREDATE" "HIREDATE","A1"."SAL" "SAL","A1"."COMM" "COMM","A1"."DEPTNO"
"DEPTNO" FROM  (SELECT "A2"."EMPNO" "EMPNO","A2"."ENAME" "ENAME","A2"."JOB" "JOB
","A2"."MGR" "MGR","A2"."HIREDATE" "HIREDATE","A2"."SAL" "SAL","A2"."COMM" "COMM
","A2"."DEPTNO" "DEPTNO" FROM SCOTT."EMP" "A2" WHERE "A2"."DEPTNO"=10) "A1"

-- 2-2
SELECT "A1"."EMPNO" "EMPNO"
     , "A1"."ENAME" "ENAME"
     , "A1"."JOB" "JOB"
     , "A1"."MGR" "MGR"
     , "A1"."HIREDATE" "HIREDATE"
     , "A1"."SAL" "SAL"
     , "A1"."COMM" "COMM"
     , "A1"."DEPTNO" "DEPTNO"
  FROM (SELECT "A2"."EMPNO" "EMPNO"
             , "A2"."ENAME" "ENAME"
             , "A2"."JOB" "JOB"
             , "A2"."MGR" "MGR"
             , "A2"."HIREDATE" "HIREDATE"
             , "A2"."SAL" "SAL"
             , "A2"."COMM" "COMM"
             , "A2"."DEPTNO" "DEPTNO"
          FROM SCOTT."EMP" "A2"
         WHERE "A2"."DEPTNO" = 10) "A1"

 

3-1처럼 뷰가 포함되지 않은 비교적 복잡한 쿼리를 입력해보자. 포맷팅한 결과인 3-2를 보면 주석이 제거되고 조건절이 수정되었음을 확인할 수 있다.

-- 3-1
DECLARE
    l_sql_text VARCHAR2(32767) := q'[
SELECT a.deptno
     , a.dname
  -- , a.loc
  FROM dept a
 WHERE a.loc = :v_loc
   AND EXISTS (SELECT /*+ UNNEST NL_SJ */
                      'X'
                 FROM emp x
                WHERE x.deptno = a.deptno
                  AND x.hiredate >= DATE '1985-01-01'
                  AND x.sal BETWEEN 1000 AND 2500)
]';

BEGIN
    DBMS_UTILITY.EXPAND_SQL_TEXT (input_sql_text => l_sql_text , output_sql_text => :v_sql_text);
END;
/

PRINT v_sql_text

V_SQL_TEXT
--------------------------------------------------------------------------------
SELECT "A1"."DEPTNO" "DEPTNO","A1"."DNAME" "DNAME" FROM "SCOTT"."DEPT" "A1" WHER
E "A1"."LOC"=:B1 AND  EXISTS (SELECT /*+ NL_SJ UNNEST */ 0 FROM "SCOTT"."EMP" "A
2" WHERE "A2"."DEPTNO"="A1"."DEPTNO" AND "A2"."HIREDATE">=TO_DATE(' 1985-01-01 0
0:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A2"."SAL">=1000 AND "A2"."SAL"<=2500)

-- 3-2
SELECT "A1"."DEPTNO" "DEPTNO"
     , "A1"."DNAME" "DNAME"
  FROM "SCOTT"."DEPT" "A1"
 WHERE "A1"."LOC" = :b1
   AND EXISTS (SELECT /*+ NL_SJ UNNEST */
                      0
                 FROM "SCOTT"."EMP" "A2"
                WHERE "A2"."DEPTNO" = "A1"."DEPTNO"
                  AND "A2"."HIREDATE" >= TO_DATE (' 1985-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
                  AND "A2"."SAL" >= 1000
                  AND "A2"."SAL" <= 2500)