Re: dba_dependencies "extra" rows?

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 8 Jun 2005 19:26:18 -0700
Message-ID: <1118283978.059718.209310@g44g2000cwa.googlegroups.com>

Oracle has a utlity that does something similar, that can be installed by running utldtree.sql. It includes a deptree_fill procedure. With a little modification of the deptree_fill procedure, you can make your own reverse_deptree_fill procedure, that should do about what you want.  You may want to add some additional modifications or filter the result set when you query. Please see the demonstration below.

scott_at_ORA92> -- function and views for demonstration: scott_at_ORA92> CREATE OR REPLACE FUNCTION funca   2 RETURN NUMBER
  3 AS
  4 BEGIN
  5 RETURN 1;
  6 END funca;
  7 /

Function created.

scott_at_ORA92> SHOW ERRORS
No errors.
scott_at_ORA92> CREATE OR REPLACE VIEW viewc AS SELECT funca () some_col FROM DUAL
  2 /

View created.

scott_at_ORA92> CREATE OR REPLACE VIEW viewb AS SELECT * FROM viewc   2 /

View created.

scott_at_ORA92> CREATE OR REPLACE VIEW viewa AS SELECT * FROM viewb   2 /

View created.

scott_at_ORA92> -- install Oracle utility
scott_at_ORA92> -- (substutiting your own Oracle home directory path):
scott_at_ORA92> START d:\oracle\ora92\rdbms\admin\utldtree.sql
scott_at_ORA92> -- output of installation ommitted to save space
scott_at_ORA92> -- create reverse_deptree_fill procedure
scott_at_ORA92> -- as modification of deptree_fill procedure:
scott_at_ORA92> create or replace procedure reverse_deptree_fill
  2    (type   char,
  3  	schema char,
  4  	name   char)

  5 is
  6 obj_id number;
  7 begin
  8 delete from deptree_temptab;
  9 commit;
 10 select object_id into obj_id from all_objects

 11  	 where owner	    = upper(reverse_deptree_fill.schema)
 12  	 and   object_name  = upper(reverse_deptree_fill.name)
 13  	 and   object_type  = upper(reverse_deptree_fill.type);
 14    insert into deptree_temptab
 15  	 values(obj_id, 0, 0, 0);
 16    insert into deptree_temptab
 17  	 select referenced_object_id, object_id,
 18  	     level, deptree_seq.nextval
 19  	   from public_dependency
 20  	   connect by prior referenced_object_id = object_id
 21  	   start with object_id = reverse_deptree_fill.obj_id;

 22 exception
 23 when no_data_found then

 24  	 raise_application_error
 25  	    (-20000,
 26  	     type || ' ' || schema || '.' || name || ' was not found.');

 27 end reverse_deptree_fill;
 28 /

Procedure created.

scott_at_ORA92> show errors
No errors.

scott_at_ORA92> -- execute the reverse_deptree_fill procedure
scott_at_ORA92> -- to populate the deptree_temptab table:
scott_at_ORA92> EXECUTE reverse_deptree_fill ('VIEW', 'SCOTT', 'VIEWA')

PL/SQL procedure successfully completed.

scott_at_ORA92> -- select from the deptree_temptab table: scott_at_ORA92> SELECT * FROM deptree_temptab   2 /

 OBJECT_ID REFERENCED_OBJECT_ID NEST_LEVEL SEQ#

---------- -------------------- ---------- ----------
     89801                    0          0          0
     89800                89801          1         89
     89799                89800          2         90
     64763                89799          3         91
       223                89799          3         92
       222                89799          3         93
     89750                89799          3         94
       647                89750          4         95
     89750                89800          2         96
       647                89750          3         97
     89750                89801          1         98
       647                89750          2         99

12 rows selected.

scott_at_ORA92> -- select from the deptree view: scott_at_ORA92> SELECT * FROM deptree
  2 /

NESTED_LEVEL TYPE SCHEMA NAME SEQ# ------------ -------- -------- -------- ----------

           3 TABLE    SYS      DUAL             93
           3 SYNONYM  PUBLIC   DUAL             92
           4 PACKAGE  SYS      STANDARD         95
           2 PACKAGE  SYS      STANDARD         99
           3 PACKAGE  SYS      STANDARD         97
           3                                    91
           3 FUNCTION SCOTT    FUNCA            94
           1 FUNCTION SCOTT    FUNCA            98
           2 FUNCTION SCOTT    FUNCA            96
           2 VIEW     SCOTT    VIEWC            90
           1 VIEW     SCOTT    VIEWB            89
           0 VIEW     SCOTT    VIEWA             0

12 rows selected.

scott_at_ORA92> -- select from the ideptree view: scott_at_ORA92> SELECT * FROM ideptree
  2 /

DEPENDENCIES

---

VIEW SCOTT.VIEWA
   VIEW SCOTT.VIEWB

      VIEW SCOTT.VIEWC
         <no permission>
         SYNONYM PUBLIC.DUAL
         TABLE SYS.DUAL
         FUNCTION SCOTT.FUNCA
            PACKAGE SYS.STANDARD
      FUNCTION SCOTT.FUNCA
         PACKAGE SYS.STANDARD
   FUNCTION SCOTT.FUNCA
      PACKAGE SYS.STANDARD

12 rows selected.

scott_at_ORA92> Received on Wed Jun 08 2005 - 21:26:18 CDT