sql to find references to a table

hi

Is there a sql to find out all the store procedures which are using a table?

thanks
royjaydAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Works for me:
create or replace function myfunc return varchar2 
is
begin
	return 'Hello';
end;
/

show errors

create or replace procedure myproc
is
begin
	dbms_output.put_line(myfunc);
end;
/

show errors


SELECT * FROM all_dependencies WHERE  referenced_name = 'MYFUNC' AND type IN ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TYPE');

Open in new window


Does your setup resemble my test case?
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
The view you need is DBA_DEPENDENCIES.

http://www.dba-oracle.com/d_dba_dependencies.htm
0
 
Christoffer SwanströmConnect With a Mentor PartnerCommented:
SELECT
  *
FROM
  dba_dependencies
WHERE
  referenced_name = 'YOUR_TABLE_NAME'
AND
  type IN ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
;

This will show you all packages, procedure and functions that reference your table.

Note that you will NOT be able to find procedures, functions or packages that reference a table using dynamic SQL. E.g. if your procedure contains EXECUTE IMMEDIATE 'TRUNCATE YOUR_TABLE_NAME', the above query will not show this dependency.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Christoffer SwanströmConnect With a Mentor PartnerCommented:
If you want to find procedure, functions or packages where the name of the table is hard coded in a dynamic SQL statement you can query all_source:

SELECT
  *
FROM
  all_source
WHERE
  UPPER(text) LIKE '%YOUR_TABLE_NAME%'
;

Of course this will only find the cases where the table name is hard coded. If it is passed as an argument or otherwise generated dynamically you will not find it.
0
 
royjaydAuthor Commented:
thanks

when i do
SELECT
  *
FROM
  dba_dependencies
WHERE
  referenced_name = 'PERSON_DATA'  AND
  type IN ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
;

i get error table does not exist

however when i replace dba_dependencies with all_dependencies
i get some results

why is that
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
"dba_" level views require DBA level access and show EVERYTHING in the database

"all_" level views are the objects you own and have rights to access

"user_" views are the objects you own.
0
 
awking00Connect With a Mentor Commented:
It's because you don't have permissions to access the dba dictionary tables or views. You need to have the 'select any dictionary' privilege.
0
 
royjaydAuthor Commented:
>>>It's because you don't have permissions to access the dba dictionary tables or views. You need to have the 'select any dictionary' privilege.


what exactly do you mean by dictionary ? Do you mean schema ?


thx all.
0
 
slightwv (䄆 Netminder) Commented:
The data dictionary are all the system views/tables about things (metadata).  A schema is a user that owns objects.  Two different things.

http://en.wikipedia.org/wiki/Data_dictionary
0
 
royjaydAuthor Commented:
one quick question

when i run
SELECT * FROM all_dependencies WHERE  referenced_name = 'GET_STATUS' AND type IN ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TYPE');

i get no data found

But i see 'GET_STATUS' is defined under function which is being used by a procedure body.

any idea why?
0
 
royjaydAuthor Commented:
>>Does your setup resemble my test case?


Does it have to do something with grants? Do i need to have grants to all the functions ?
0
 
royjaydAuthor Commented:
and yes,.. my set up is exactly like yours
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
If it was a permission issue you would get a permissions error.

Remember the ALL_ views are just objects you either own or have been granted access to.

What is the result of:
select owner, object_type from all_objects where object_name='GET_STATUS';
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.