Query to get all the dependent objects and their traverse paths of a job

[Former Member]

For a given job this query returns all the dependent objects and their traverse paths.(Job name should be given in
the outer where clause <<JOB_NAME>>)

SELECT JOB_NAME

   ,   OBJECT

   ,   OBJECT_TYPE

   ,   PATH

FROM

(

SELECT Other_Objects.DESCEN_OBJ OBJECT

     , Other_Objects.DESCEN_OBJ_USAGE OBJECT_TYPE

     , Connection_Path1.PATH || Other_Objects.DESCEN_OBJ || '( ' || Other_Objects.DESCEN_OBJ_USAGE || ' ) ' PATH

     , substr(Connection_Path1.PATH, instr(Connection_Path1.PATH, ' ->> ', 1)+5 , instr(Connection_Path1.PATH, ' ->> ', 2)-(instr(Connection_Path1.PATH, ' ->> ', 1)+5)) JOB_NAME

FROM

(

SELECT DISTINCT PARENT_OBJ

      , PARENT_OBJ_TYPE

      , SYS_CONNECT_BY_PATH(PARENT_OBJ,' ->> ')|| ' ->> ' PATH

FROM ALVW_PARENT_CHILD

START WITH PARENT_OBJ_TYPE = 'Job'

CONNECT BY PRIOR DESCEN_OBJ = PARENT_OBJ

) Connection_Path1,

(

SELECT  PARENT_OBJ

      , PARENT_OBJ_TYPE

      , DESCEN_OBJ

      , DESCEN_OBJ_USAGE

FROM ALVW_PARENT_CHILD

WHERE PARENT_OBJ_TYPE = 'DataFlow'

and

DESCEN_OBJ_TYPE = 'Table'

)Other_Objects

WHERE

Connection_Path1.PARENT_OBJ = Other_Objects.PARENT_OBJ

AND

Connection_Path1.PARENT_OBJ_TYPE = Other_Objects.PARENT_OBJ_TYPE

UNION

SELECT Connection_Path2.PARENT_OBJ OBJECT

     , Connection_Path2.PARENT_OBJ_TYPE OBJECT_TYPE

     , Connection_Path2.PATH PATH

     , substr(Connection_Path2.PATH, instr(Connection_Path2.PATH, ' ->> ', 1)+5 , instr(Connection_Path2.PATH, ' ->> ', 2)-(instr(Connection_Path2.PATH, ' ->> ', 1)+5)) JOB_NAME

FROM

(

SELECT DISTINCT PARENT_OBJ

      , PARENT_OBJ_TYPE

      , SYS_CONNECT_BY_PATH(PARENT_OBJ,' ->> ')|| ' ->> ' PATH

FROM ALVW_PARENT_CHILD

START WITH PARENT_OBJ_TYPE = 'Job'

CONNECT BY PRIOR DESCEN_OBJ = PARENT_OBJ

) Connection_Path2

) WHERE

JOB_NAME LIKE <<JOB_NAME>>