Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

sql lineage of a package

537632Sep 23 2008 — edited Oct 8 2008
Hope you guys can help me with something;
i want to visualize the objects that a package uses,
and the objects that those objects use

In other words:
I want to have all the objects and child objects that make use of a package
So if i want have a package WEBSITE i want to display all the tables, views and other packages that are used in package website,
and form all those views / tables and packages all the child objects that make use of those table / views and packages.

I thought the connect by would do it for me, like below, but it taking for ever to load; i think it is in a loop.
How would you guys solve this one?
This is what i have so far:

select * from dba_dependencies where name = 'WEBSITE'
and referenced_type != 'NON-EXISTENT'
and referenced_owner != 'SYS'
and referenced_type != 'SYNONYM'
and owner||name != referenced_owner|| referenced_name
connect by owner||name = referenced_owner||referenced_name;
This post has been answered by 440432 on Oct 2 2008
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 5 2008
Added on Sep 23 2008
15 comments
1,042 views