DBMS_UTILITY.EXPAND_SQL_TEXT 프로시저

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

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

1 2	-- 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 *가 테이블 앨리어스가 포함된 칼럼으로 표시된 점도 흥미롭다.  

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

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를 보면 주석이 제거되고 조건절이 수정되었음을 확인할 수 있다.

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

-- 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)