[SQL] 오라클(Oracle) 패키지 내에서 사용하는 테이블 추출 방법

오라클 패키지를 분석하다보면 사용하고 있는 테이블이 무엇인지 알고 싶을 때가 많습니다.

dba_dependencies 딕셔너리를 이용해서 확인 하셔도 되지만, 더 나아가서 패키지 안에 프로시저별로 테이블을 뽑고 싶을 때 사용하시면 됩니다.

[ 쿼리 ]

SELECT X.OWNER, 
       X.NAME, 
       X.TYPE, 
       X.PROCEDURE_NAME, 
       X.TABLE_NAME, 
       X.LINE 
       || ' ~ ' 
       || X.LINE_NEXT AS LINE, 
       X.TEXT         AS ORG_PROCEDURE_NAME 
FROM   (SELECT A.*, 
               Trim(Regexp_substr(A.TEXT, '(PROCEDURE\s+)(.+)(\()', 
                    --> subexpressions ? ??? ??? 
                    1, --> ???? 
                    1, --> ???? 
                    'i', --> Matching Modifiers 
                    2 --> 11g: subexpression to return as procedure_name 
                    )) AS PROCEDURE_NAME, 
               CASE 
                 WHEN B.REFERENCED_LINK_NAME IS NOT NULL THEN 
                 B.REFERENCED_NAME 
                 || '@' 
                 || B.REFERENCED_LINK_NAME 
                 ELSE B.REFERENCED_NAME 
               END     TABLE_NAME 
        FROM   (SELECT /*+ NO_MERGE */ A.OWNER, 
                                       A.NAME, 
                                       A.TYPE, 
                                       A.LINE, 
                                       Lead(A.LINE, 1, 1000000) 
                                         OVER ( 
                                           ORDER BY A.LINE) LINE_NEXT, 
                                       A.TEXT 
                FROM   DBA_SOURCE A 
                WHERE  A.OWNER = :in_owner 
                       AND A.NAME = :in_pkg_name 
                       AND Regexp_like(A.TEXT, 'PROCEDURE', 'i') 
                       AND ( A.TYPE = 'PACKAGE BODY' 
                              OR A.TYPE = 'PROCEDURE' )) A, 
               DBA_DEPENDENCIES B 
        WHERE  B.OWNER = A.OWNER 
               AND B.NAME = A.NAME 
               AND B.TYPE = A.TYPE 
               AND B.REFERENCED_TYPE = 'TABLE') X 
WHERE  EXISTS (SELECT 'O' 
               FROM   DBA_SOURCE S 
               WHERE  S.OWNER = X.OWNER 
                      AND S.NAME = X.NAME 
                      AND S.TYPE = X.TYPE 
                      AND S.LINE BETWEEN X.LINE AND X.LINE_NEXT 
                      --                   and      regexp_like(s.text, x.table_name||'(\s|\n)+', 'i') 
                      AND Regexp_like(S.TEXT, X.TABLE_NAME, 'i')) 
ORDER  BY X.LINE, 
          X.PROCEDURE_NAME, 
          X.TABLE_NAME