리소스 의존성 검색 SQL

BM의  CALL DEPTH 가 1 인 것만 검색하는 SQL 은 다음과 같다.
사실 의존성 검색은 1레벨 의존성만 검색해도 충분하다.

SELECT RES.RESOURCE_ID,
       RES.PHYSICAL_NAME,
       RES.LOGICAL_NAME,
       RES.RESOURCE_TYPE,
       RES.RESOURCE_GROUP,
       RES.OWNER,
       RES.UPDATE_TIME,
       RES.STATUS
  FROM DEV_RESOURCE RES,
       (SELECT DISTINCT
               RESOURCE_ID
          FROM DEV_PROPERTY
        WHERE REFERENCE_ID = (select resource_id from dev_resource where physical_name = 'mnlgs8030_0')
         ) PRO
 WHERE RES.RESOURCE_ID = PRO.RESOURCE_ID
 ORDER BY RES.PHYSICAL_NAME

좀더 응용하여 오너의 이름까지 나오게 하고 싶다면 dev_user_info 와 조인을 하면 되겠다.

SELECT A.PHYSICAL_NAME, A.LOGICAL_NAME, A.RESOURCE_GROUP, A.OWNER, USER.USER_NAME  FROM (
        SELECT RES.RESOURCE_ID, RES.PHYSICAL_NAME, RES.LOGICAL_NAME,
               RES.RESOURCE_TYPE, RES.RESOURCE_GROUP, RES.OWNER OWNER,
               RES.UPDATE_TIME,     RES.STATUS
          FROM DEV_RESOURCE RES,
               (SELECT DISTINCT
                       RESOURCE_ID
                  FROM DEV_PROPERTY 
                WHERE REFERENCE_ID = (SELECT RESOURCE_ID FROM DEV_RESOURCE
                                      WHERE PHYSICAL_NAME IN ( :physical_name))
                 ) PRO
         WHERE RES.RESOURCE_ID = PRO.RESOURCE_ID
  )  A,
  DEV_USER_INFO USER
WHERE USER.OWNER = USER.USER_ID(+)
ORDER BY PHYSICAL_NAME