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.

try out `dba_dependencies` like `select * from dba_dependencies where name = 'YOUR_OBJECT'` — Exhausted, Jan 20 '15 at 11:42
It really doesn't help if I use dba_dependencies instead of all_dependencies. — pcejrowski, Jan 20 '15 at 17:49

score 2 · Accepted Answer · edited May 23 '17 at 12:25

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.

Thanks for this brutal clarification :) – pcejrowski Jan 21 '15 at 07:58 — pcejrowski, Jan 21 '15 at 07:58

Oracle database dependencies in PL/SQL

1 Answers1

Linked