오라클 패키지 내의 프로시저별로 사용하고 있는 테이블을 알고자 할 경우 유용한 쿼리 입니다.
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
출처: https://estenpark.tistory.com/330 [DATA 전문가로 가는 길]