Oracle Database Discussions

I’m trying to visualize the dependencies of the database objects in a certain schema in an 11.2.0.3 database with the tool graphviz (see http://graphviz.org). So I wrote a small query to create a script for graphviz based on the information that I find in the view dba_dependencies (for those who are interested I attach my first version of this query at the end of this posting.) In the result I found some dependencies that I did not expect.

If you take this example, which you should create in a blank schema (I called my schema DEMO):

create table t1
(id  number, val number);
 
create view v1 as 
select 
  id, val *2 val1
from t1;

create function f1(v in number) 
return number is
begin
   return v * 3;
end;
/

create function f2(i in number) 
return number is
v number;
begin
   select val1 into v from v1 where id = i;
   return(v);
end;
/

create view v2 as 
select 
  id, f1(val1) val2
from v1;

create view v3 as
select 
  id, val2 * 4 val3
from v2;

create view v4 as
select
  id, f2(id) val4
from t1;

create view v5 as
select 
  id, val4 * 5 val5
from v4;

and you look how this is represented in dba_dependencies:

select * from 
sys.dba_dependencies
where referenced_owner = 'DEMO';

OWNER NAME TYPE     REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE    REFERENCED_LINK_NAME DEPENDENCY_TYPE
----- ---- -------- ---------------- --------------- ------------------ -------------------- ---------------
DEMO  F2   FUNCTION DEMO             V1              VIEW                                    HARD         
DEMO  V1   VIEW     DEMO             T1              TABLE                                   HARD         
DEMO  V2   VIEW     DEMO             F1              FUNCTION                                HARD         
DEMO  V2   VIEW     DEMO             V1              VIEW                                    HARD         
DEMO  V3   VIEW     DEMO             F1              FUNCTION                                HARD         
DEMO  V3   VIEW     DEMO             V2              VIEW                                    HARD         
DEMO  V4   VIEW     DEMO             F2              FUNCTION                                HARD         
DEMO  V4   VIEW     DEMO             T1              TABLE                                   HARD         
DEMO  V5   VIEW     DEMO             F2              FUNCTION                                HARD         
DEMO  V5   VIEW     DEMO             V4              VIEW                                    HARD         

it shows that the views v3 and v5 are dependent from the functions f1 and f2 respectively. But in my opinion v3 and v5 are only (directly) dependent from the view v2 and v4 and only those are dependent from the functions. In the other cases dba_dependencies shows only the direct dependencies and not the indirect ones.

Why do I see these indirect dependencies here?
Is there a certain reason or is it a bug?

They give me a lot of unwanted lines in my graph, which I created with a query like this:

with a as
(select o1.object_id    id_1,
        o2.object_id    id_2,
        o1.owner        owner_1,
        o2.owner        owner_2,
        o1.object_type  type_1,
        o2.object_type  type_2,
        o1.object_name  name_1,
        o2.object_name  name_2
      from SYS.dba_dependencies dep
      join sys.dba_objects o1 on dep.referenced_owner = o1.owner and dep.referenced_name = o1.object_name and dep.referenced_type = o1.object_type
      join sys.dba_objects o2 on dep.owner = o2.owner and dep.name = o2.object_name and o2.object_type = o2.object_type
     where dep.referenced_owner in ('DEMO')
),
b as
(
   select 'Digraph G {' zeile, 10 sorter from dual
 union
   select 'rankdir = LR;', 15 from dual
 union
   select 'ID'||to_char(id_1)||' [shape=box, style = filled, fillcolor = '||
                                         case type_1 
                                            when 'TABLE'     then 'burlywood1'
                                            when 'FUNCTION'  then 'cadetblue1'    
                                            when 'VIEW'      then 'coral1'
                                            when 'PROCEDURE' then 'gold1'
                                                             else 'greenyellow' 
                                         end
                                         ||', label="'||type_1||'\n'||owner_1||'.'||name_1||'"];',20 
    from a
  union
    select 'ID'||to_char(id_2)||' [shape=box, style = filled, fillcolor = '||
                                         case type_2 
                                            when 'TABLE'     then 'burlywood1'
                                            when 'FUNCTION'  then 'cadetblue1'    
                                            when 'VIEW'      then 'coral1'
                                            when 'PROCEDURE' then 'gold1'
                                                             else 'greenyellow' 
                                         end
                                         ||', label="'||type_2||'\n'||owner_2||'.'||name_2||'"];',20
    from a
  union
    select 'ID'||to_char(id_1)||' -> ID'||to_char(id_2)||';',40
      from a
  union
    select '}' zeile, 99 sorter from dual
)
select zeile from b order by sorter;