0

I am looking for a simple Oracle SQL query: I want to know in which packages in the Oracle database a specific table is been used. E.g

select * 
from   abc
where  table.name = 'XYZ'

The output should show me all packages for this table.

APC
  • 144,005
  • 19
  • 170
  • 281
HamsterBoomer
  • 1
  • 1
  • Does this answer your question? [How to see PL/SQL Stored Function body in Oracle](https://stackoverflow.com/questions/14212295/how-to-see-pl-sql-stored-function-body-in-oracle) – Ori Marko Aug 17 '21 at 15:39
  • @user7294900 - no, that thread does not properly answer this Seeker's question. A better answer would mention USER_DEPENDENCIES as the first port of call. I would only turn to USER_SOURCE if I knew (or suspected) the code base made use of dynamic SQL – APC Aug 18 '21 at 11:34

1 Answers1

4

Give this a try:

SELECT *
FROM   ALL_DEPENDENCIES
WHERE  referenced_name = 'YOUR_TABLE_NAME'
AND    owner           = 'YOUR_USER'
and type = 'PACKAGE BODY';
OldProgrammer
  • 12,050
  • 4
  • 24
  • 45
  • 1
    I would expand that to "AND TYPE IN ('PACKAGE BODY', 'PROCEDURE'". I know the OP said specifically for 'packages' but I suspect he is overlooking the possibility of procedures that are not in a package. Of course, he's also overlooking packages and procedures that (poorly) use dynamic sql to resolve table names at run time . . . :-) – EdStevens Aug 17 '21 at 18:49
  • 1
    @EdStevens - if we're expanding the criteria to standalone units we should include 'FUNCTION' as well. Also worth noting that occasionally a package spec may reference a table using a `%rowtype` declaration without its body using the table at all. The point about dynamic SQL is a good one. It's just another reason for avoiding the use of dynamic SQL unless it's absolutely necessary. – APC Aug 18 '21 at 11:30