How to find all table references from Oracle 10G PL/SQL functions and procedures?

Question

How to find all table references from Oracle 10G PL/SQL functions and procedures?

I definitely can execute the following SQL statement:

select * from dba_source where text like '%tbl_c%'

but I wonder how to find all functions that call functions that refer to table used. For example I can have a function A that calls function B that uses table tbl_c. If I'll execute aforementioned SQL I'll find funciton B but then I have to execute another SQL to find A. As you know the cyclomatic complexity could be 3,4,5 levels deep or even greater.

Greatly appreciate in advance for your explanation.

Answer 1

I'd prefer to use ALL_DEPENDENCIES when looking for object references rather than ALL_SOURCE. Adding to a hierarchical query, you could get what ever you need.

Nicolas.

Answer 2

dba_dependencies is where to start. Example:

 SELECT      owner
             || '.'
             || NAME
             || ' ('
             || DECODE (TYPE,
                        'MATERIALIZED VIEW', 'MV',
                        'DIMENSION', 'DIM',
                        'EVALUATION CONTXT', 'EVALCTXT',
                        'PACKAGE BODY', 'PKGBDY',
                        'CUBE.DIMENSION', 'CUBE.DIM',
                        TYPE
                       )
             || ')' objdep,
                referenced_name
             || ' ('
             || DECODE (referenced_type,
                        'EVALUATION CONTXT', 'EVALCTXT',
                        'NON-EXISTENT CONTXT', 'NO-EXIST',
                        'PACKAGE BODY', 'PKGBDY',
                        'CUBE.DIMENSION', 'CUBE.DIM',
                        referenced_type
                       )
             || ')' refr
        FROM dba_dependencies
       WHERE owner = :usn
    ORDER BY objdep;