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

how can find out stored procedure foo called (names of calling procedures)? i'm trying find out in type of situations foo called, , seems cumbersome query select * user_source because there string literals contain foo.

regards, robotron

you can use all_dependencies view:

select * all_dependencies  referenced_owner = <username> , referenced_name = 'foo'; 

this return objects directly use procedure foo. if procedure uses foo inside dynamic sql statement, however, you'll either have parse source, or try adding logging call stack inside foo procedure (in real application, logged table instead of dbms_output):

create or replace procedure foo begin   dbms_output.put_line('foo');   dbms_output.put_line(dbms_utility.format_call_stack());   end;  create procedure bar 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, work if code that's using function called :-)