패키지 내에서 사용 테이블 정보 출력

--1. 패키지 내에서 테이블이 어느 프로시저에서 사용하고 있는지 정보를 출력
--2. 프로시저 위치 정보와 테이블 위치정보를 가지는 테이블 생성해서 최종적으로 데어터 추출
--   TABLE : PACKAGE_PROCEDURE_LIST, PACKAGE_TABLE_LOC_LIST
--3. 해당 쿼리를 프로젝트에 맞게 쿼리를 약간 수정해서 사용


--패키지 내 프로시저 위치 정보 테이블을 생성
CREATE TABLE PACKAGE_PROCEDURE_LIST AS 
SELECT 
        NAME
      , TYPE
      , LINE
      , TEXT
      , OBJECT_NAME
      , PROCEDURE_NAME 
FROM 
(
      SELECT 
              A.NAME
            , A.TYPE
            , A.LINE
            , TRIM(REPLACE(REPLACE(REPLACE(REPLACE(A.TEXT,'PROCEDURE',''),'(',''),' ', ''),CHR(10),'')) AS TEXT
            , B.OBJECT_NAME
            , B.PROCEDURE_NAME
        FROM USER_SOURCE A,
             (
              SELECT OBJECT_NAME, PROCEDURE_NAME 
                FROM USER_PROCEDURES
               WHERE OBJECT_NAME LIKE 'PACKAGENM%' --사용하는 패키지명 
                 AND PROCEDURE_NAME IS NOT NULL
             ) B
       WHERE A.NAME = B.OBJECT_NAME
         AND A.TYPE = 'PACKAGE BODY'
         AND UPPER(A.TEXT) LIKE '%PROCEDURE%'||B.PROCEDURE_NAME||'%'
      ORDER BY A.NAME, A.LINE
)X 
WHERE 1=1
  AND X.TEXT = X.PROCEDURE_NAME;


-- 패키지 내에서 사용하고 있는 테이블 위치
CREATE TABLE PACKAGE_TABLE_LOC_LIST AS 
  SELECT /*+ PARALLEL(A 8) */ *
    FROM USER_SOURCE A,
        ( 
          SELECT OBJECT_NAME 
            FROM USER_OBJECTS
           WHERE ( OBJECT_NAME LIKE 'EMP_%' OR OBJECT_NAME LIKE 'INFO_%' ) --EMP, INFO로 시작하는 테이블 및 VIEW 검색 조건
             AND OBJECT_TYPE IN ('TABLE', 'VIEW')
        ) B
  WHERE 1=1 
    AND  A.NAME LIKE 'PACKAGENM%'
    AND INSTR(UPPER(A.TEXT), B.OBJECT_NAME ) > 0
    AND NVL(TRIM(SUBSTR(UPPER(A.TEXT), INSTR(UPPER(A.TEXT), B.OBJECT_NAME )- 1 ,1)),'.') = '.' 
    AND TRIM(SUBSTR(UPPER(REPLACE(A.TEXT,CHR(10),'')), INSTR(UPPER(REPLACE(A.TEXT,CHR(10),'')), B.OBJECT_NAME ) + LENGTH(B.OBJECT_NAME),1)) IS  NULL 
    AND EXISTS ( 
                  SELECT 'X' 
                    FROM PACKAGE_PROCEDURE_LIST X 
                   WHERE A.NAME = X.NAME 
    );
            
-- TABLE : PACKAGE_PROCEDURE_LIST, PACKAGE_TABLE_LOC_LIST을 이용하여 
-- 패키지내 테이블 사용 정보를 출력
SELECT DISTINCT 
       PACKAGE_NM    AS "패키지명"
      ,PROC_NM       AS "프로시저명"
      ,TABLE_NM      AS "테이블명"
      ,T.COMMENTS    AS "테이블설명"
      --,TABLE_LOC
      --,PROC_LOC
      --,PROC_MAXLINE 
 FROM  
 (
       SELECT 
              A.NAME                                                         AS PACKAGE_NM
             ,B.PROCEDURE_NAME                                               AS PROC_NM
             ,A.OBJECT_NAME                                                  AS TABLE_NM
             ,A.LINE                                                         AS TABLE_LOC
             ,B.LINE                                                         AS PROC_LOC
             ,MAX(B.LINE) OVER ( PARTITION BY A.NAME, A.LINE )               AS PROC_MAXLINE
         FROM 
              PACKAGE_TABLE_LOC_LIST A
             ,PACKAGE_PROCEDURE_LIST B
       WHERE 1=1
         AND A.NAME  = B.NAME
         AND B.LINE <= A.LINE
)X, USER_TAB_COMMENTS T
WHERE 1=1
  AND X.PROC_LOC = X.PROC_MAXLINE
  AND X.TABLE_NM = T.TABLE_NAME
ORDER BY PACKAGE_NM, PROC_NM, TABLE_NM;