How do you programatically identify a stored procedure's dependencies?

Question

Is it possible to write a PL/SQL query to identify a complete list of a stored procedures dependencies? I'm only interested in identifying other stored procedures and I'd prefer not to limit the depth of nesting that it gets too either. For example, if A calls B, which calls C, which calls D, I'd want B, C and D reported as dependencies for A.

Answer 1

On this page, you will find the following query which uses the PUBLIC_DEPENDENCY dictionary table:

 SELECT lvl
     , u.object_id
     , u.object_type
     , LPAD (' ', lvl) || object_name obj
   FROM ( SELECT LEVEL lvl, object_id
            FROM SYS.public_dependency s
         START WITH s.object_id =
                      ( SELECT object_id
                          FROM user_objects
                         WHERE object_name = UPPER ('&OBJECT_NAME')
                           AND object_type = UPPER ('&OBJECT_TYPE'))
         CONNECT BY s.object_id = PRIOR referenced_object_id
         GROUP BY LEVEL, object_id) tree
      , user_objects u
  WHERE tree.object_id = u.object_id
ORDER BY lvl
/

Answer 2

I agree with EddieAwad.

Its valuable to point out that Oracle only tracks the dependencies down to the object level. If you have your stored procedures in a package you can only track the dependencies if the package, not the individual functions/procedures within the package.

If you're looking to track intra-package dependencies then you'll need a PLSQL parser.

Answer 3

Something else worth pointing out here, is that there are certain cases where the dependency may only be at runtime, which unfortunately will not show up in the metadata.

For example, if you are constructing a SQL statement at runtime, you may have code similar to:

...
mysql := 'select count(*) from '||table_name_in;
execute immediate mysql;
...

I've been burnt by this a few times, but there's unfortunately no way to find these types of dependencies in advance since it potentially depends on user input.