--    Author:              Ahbaid Gaffoor - OCPdba.Net
--    Date:                Sunday 18th August 2002
--    File:                proc_show_dependencies.sql
--    Purpose:             This procedure recursively examines DBA_DEPENDENCIES 
-- 			and prints a dependency tree for an object. 
-- 
--    Usage:               exec show_dependencies('..');
-- 
--    Example:
--                         set serveroutput on
--                         begin
--                            show_dependencies('SCOTT.EMPLOYEE.TABLE');
--                         end;
--                         /
-- 
--    Notes:               Create as SYS and create a public synonym for the procedure

create or replace procedure show_dependencies(objectstr varchar2, plevel number := 0) as

   fowner varchar2(30);
   fname varchar2(30);
   ftype varchar2(30);

   cursor c1 (f1 varchar2, f2 varchar2, f3 varchar2) is 
                select owner, name, type
                from dba_dependencies
                where referenced_owner = f1 and
                      referenced_name = f2 and
                      referenced_type = f3;

   c1var c1%ROWTYPE;

begin

   fowner := substr(objectstr,1,instr(objectstr,'.',1,1)-1);
   fname  := substr(objectstr,instr(objectstr,'.',1,1)+1,instr(objectstr,'.',1,2)-instr(objectstr,'.',1,1)-1);
   ftype  := substr(objectstr,instr(objectstr,'.',1,2)+1);


   open c1(fowner,fname,ftype);
   loop
      fetch c1 into c1var;
      exit when c1%NOTFOUND;

         dbms_output.put_line(lpad('=',plevel+2,'=')||c1var.owner||'.'||c1var.name||'-'||c1var.type);
         show_dependencies(c1var.owner||'.'||c1var.name||'.'||c1var.type,plevel+1);

   end loop;
   close c1;

end;
/
show errors

grant execute on show_dependencies to public;
create public synonym show_dependencies for show_dependencies;