CODEWITHSUNDEEP
oracle-databasestored-procedures
Robotronx
Robotronx

Reputation: 1818

Find out where stored proc is called from - [Oracle]

How can I find out where my stored procedure FOO is called from (names of calling procedures)? I'm trying to find out in what type of situations FOO is called, and it seems very cumbersome to query SELECT * FROM user_source because there are string literals that contain FOO.

Regards, Robotron

Upvotes: 2

Views: 5032

Answers (2)

Peter Lang
Peter Lang

Reputation: 55524

If you want to get this information at runtime, try OWA_UTIL.WHO_CALLED_ME:

OWA_UTIL.WHO_CALLED_ME(
   owner          OUT      VARCHAR2,
   name           OUT      VARCHAR2,
   lineno         OUT      NUMBER,
   caller_t       OUT      VARCHAR2);

If this procedure does not exist, have a look at this post from Tom Kyte:
How Can I find out who called me or what my name is

Upvotes: 2

Frank Schmitt
Frank Schmitt

Reputation: 30765

You can use the ALL_DEPENDENCIES view:

select * from all_dependencies 
where referenced_owner = <username>
and referenced_name = 'FOO';

This will return all objects that directly use your procedure FOO. If a procedure uses FOO inside a dynamic SQL statement, however, you'll either have to parse the source, or you could try adding logging the call stack inside your FOO procedure (in a real application, this would be logged to a table instead of dbms_output):

create or replace procedure foo as
begin
  dbms_output.put_line('foo');
  dbms_output.put_line(dbms_utility.format_call_stack());
  end;

create procedure bar as
begin
  dbms_output.put_line('bar');
  foo();
end;  

begin
  bar();
end;

Output:

bar
foo
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
00000003E180A118         4  procedure SCHMITT.FOO
00000003E180E140         4  procedure SCHMITT.BAR
00000003E17FA388         2  anonymous block

However, this will only work if the code that's using your function is actually called :-)

Upvotes: 2

Related Questions