Skip to Content

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>>
To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply