Find Multiple levels of object dependencies : depen.sql

set echo off

set head off

set lines 130

set verify off

accept v_owner prompt ‘Enter Owner Name : ‘

accept v_name prompt ‘Enter Object Name : ‘

accept v_type prompt ‘Enter Object Type : ‘

prompt Dependency tree for OWNER : &v_owner, OBJECT_NAME : &v_name OBJECT_TYPE : &v_type

prompt ==========================================================================================

SELECT rpad (rpad(‘*’, LEVEL*2, ‘ ‘) || rpad( owner||’.’||name, 60, ‘ ‘), 80, ‘ ‘) || ‘-‘ || type

FROM dba_dependencies

START WITH referenced_owner = upper(‘&v_owner’)

and referenced_name = upper(‘&v_name’)

and referenced_type = upper(‘&v_type’)

CONNECT BY PRIOR owner = referenced_owner

and prior name = referenced_name

and prior type= referenced_type;

set echo on

set head on

set verify on