- CREATE OR REPLACE Function Get_Ref_Name(Pi_Name Varchar2)
- Return Varchar2 Is
- l_Name Varchar2(32);
- l_Name_String Varchar2(4000);
- Begin
- For Cur_Name In (Select aa.owner,Referenced_Name,referenced_owner
- From Dba_Dependencies aa
- Where Name = Pi_Name
- And Referenced_Owner <> 'SYS'
- And aa.referenced_name <> Pi_Name
- ) Loop
- Dbms_Output.Put_Line(cur_name.owner||'.'||Pi_Name || '-----' ||Cur_Name.referenced_owner||'.'|| Cur_Name.Referenced_Name);
- l_Name := Get_Ref_Name(Cur_Name.Referenced_Name);
- End Loop;
- Return Nvl(l_Name, 'please check output!');
- End;
2.自下往上找依赖对象
- create or replace function get_rev_ref_name(pi_name varchar2)
- return varchar2 is
- l_name varchar2(32);
- l_name_string varchar2(4000);
- begin
- for cur_name in (select name
- from dba_dependencies
- where referenced_name = pi_name
- and referenced_owner <> 'SYS') loop
- dbms_output.put_line(pi_name||'-----'||cur_name.name);
-
- l_name := get_rev_ref_name(cur_name.name);
- end loop;
- return nvl(l_name,'please check output!');
- end;
-
- select get_rev_ref_name('your object name')from dual