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

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

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

[ 쿼리 ]

?

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

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