How to identify all stored procedures refering a particular table in Oracle

Question

I am working with Oracle 12c and need to find all references where a specific table or view is being used in Stored Procedure/Function and packages.

I have found a this answer about MS SQL Server, but it's not related to Oracle, besides sp_help and sp_depends sometimes return inaccurate results.

I know to search in column text of table all_source, for example, this code (search only standard user defined package names, not system package):

SELECT   type, name, line, text 
   FROM  all_source 
   WHERE type = 'PACKAGE BODY'
     AND name like 'P%' 
     AND UPPER(text) like '%' || p_table_or_view_name || '%'
   ORDER BY name, line;

but I'm looking if there's a more elegant and/or standard solution in Oracle.

I'm also checking if this answer can help me in any way.

I will appreciate any assistance.

Answer 1

Use the ALL_DEPENDENCIES dictionary table:

SELECT *
FROM   ALL_DEPENDENCIES
WHERE  referenced_name = 'YOUR_TABLE_NAME'
AND    owner           = 'YOUR_USER';