CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
MKN
MKN

Reputation: 497

find stored procedures which uses user defined functions?

I have a user defined functions name GET_SPQ_FUNCION

which is used in many stored procedures .Now I need to remove the Input parameter from the function.It will affect many SP's. Is there any way i can list those SP's using a sql query.

Upvotes: 2

Views: 1391

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

To get a list of stored procedures which use your specific function(GET_SPQ_FUNCION() in this case), your can query(depending on the privileges granted) [all][dba][user]_dependencies view. For instance if the function GET_SPQ_FUNCION() is used by, lets say, GET_SPQ_FUNCION1() function, then issuing a similar query against one of the mentioned above views you will get the following output:

SQL> select name as usedby
  2       , type
  3       , referenced_name
  4       , dependency_type
  5    from dba_dependencies
  6    where referenced_name = 'GET_SPQ_FUNCION'
  7  ;

Usedby            Type      Referenced Name   Dependency Type 
-------------------------------------------------------------
GET_SPQ_FUNCION1  FUNCTION  GET_SPQ_FUNCION   HARD

Upvotes: 3

Related Questions