Oracle database dependencies in PL/SQL

Question

I need to find dependencies between functions/procedures(defined inside package bodies) and tables which they use.

I've tried all_dependencies but it works only on the package-level, not the inner function/procedure-level. Is there any possibilty to find this dependencies using e.g. all_source?

Thanks in advance for your help.

Answer 1

It is not possible to find the dependencies between procedures (in a package) and tables.

There are several tools to examine dependencies. As you've already discovered, *_DEPENDENCIES only tracks object dependencies on a per-package level. There is a neat tool PL/Scope that tracks dependencies between parts of a package. But it does it does not track all table references.

Theoretically you could use *_SOURCE. In practice, this is impossible unless your code uses a limited set of features. For any moderately complicated code, forget about using string functions or regular expressions to parse code. Unfortunately there does not seem to be any PL/SQL parser that is both programmable and capable of accurately parsing complex code.

Saying "it's not possible" isn't a great answer. But in this case it might save you a lot of time. This is one of those tasks where it's very easy to hit a dead end and waste a lot of effort.