How to identify all stored procedures refering a particular table in Oracle
Use the ALL_DEPENDENCIES
dictionary table:
SELECT *
FROM ALL_DEPENDENCIES
WHERE referenced_name = 'YOUR_TABLE_NAME'
AND owner = 'YOUR_USER';
Binyamin Regev
By Day: Software Engineer with more than 25 years of experience, programming is my hobby for more than 40 years. By Night: Chef and Baker, OpenSource programmer and learning languages (currently Pythod and Spanish). When I am asked how many languages I know, I start with: Java, JavaScript, TypeScript, SQL, Python, English, C#, BASIC, Pascal, Fortran, COBOL...
Updated on June 04, 2022Comments
-
Binyamin Regev almost 2 years
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
andsp_depends
sometimes return inaccurate results.I know to search in column
text
of tableall_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.