Home » SQL & PL/SQL » SQL & PL/SQL » Code Road map (Oracle 10.2)
Code Road map [message #309218] Wed, 26 March 2008 17:47 Go to next message
Messages: 7
Registered: December 2007
Junior Member
I am trying to find all the dependencies of a function. The function may call many packages,procedures,functions and may use synonyms,views,etc.. If i query the table USER_Dependencies it gives only the package but not the function inside that package that is being called.

(e.g) package A has the procedure B and function C. Function D is an independent function which uses table-T,View-V,Synonym-S and also it calls the function C inside package A.

If i query USER_DEPENDENCIES for the object 'D' it shows the dependency for the package A along with other Dependencies T,V, and S. I would like the output like 'Package~A.Function~C' I am able achieve the same using "Code Road Map" of TOAD. I would like to achieve the same using query.

Please suggest.
Re: Code Road map [message #309219 is a reply to message #309218] Wed, 26 March 2008 17:56 Go to previous messageGo to next message
Messages: 25571
Registered: January 2009
Location: SoCal
Senior Member
> Please suggest.
Start writing PL/SQL to re-implement Code Road Map of Toad.
Re: Code Road map [message #309246 is a reply to message #309219] Wed, 26 March 2008 20:58 Go to previous messageGo to next message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
To expand on Ana's explanation:

TOAD have internally parsed the code to find where references lie. The Oracle Data Dictionary does not support this level of tracking.

Ross Leishman
Re: Code Road map [message #309496 is a reply to message #309218] Thu, 27 March 2008 13:16 Go to previous message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
yes, this has been a real sore spot for many of us over the years. To this day it is not clear why Oracle, having access to all the code, and all the structures, and all the execution stats, can't (or WON'T) report for us this kind of detailed information.

As was pointed out by others, you must go to some third party tools somewhere to figure it out, or alternatively write your own code parser to do it for you (a big job and one that locks you into having to keep updating your parser of over time as pl/sql and sql change).

Here is a sampling of some popular and useful tools you might want to check:

3) SQLANALYZER (might go under some other name today)

Others may have alternative favorites.

Your question emphasizes one of the reasons why instrumentation is such a big deal.

Good luck, Kevin

Previous Topic: Row prefetch attribute and Stored procedures refcursor
Next Topic: bind variable error...WHY?
Goto Forum:

Current Time: Sat Aug 19 15:47:20 CDT 2017

Total time taken to generate the page: 0.10323 seconds