Thursday, October 08, 2009

How Can I find out who called me or what my name is

Many times we are asked "in a procedure/function, can I find out who called me" or "can I dynamically figure out the name of the procedure or package that is currently executing".

You can find it in the call stack returned by dbms_utility.format_call_stack. I wrote a small routine called who_called_me that returns this sort of information (it doesn't tell you who you are, it lets you know who called you). If you wrap who_called_me with a function who_am_i, you'll get what you need. If you create the who_called_me/who_am_i routines, you'll be able to:

SQL> create or replace procedure demo
2 as
3 begin
4 dbms_output.put_line( who_am_i );
5 end;
6 /

Procedure created.

SQL> exec demo;

In current releases of the database, this code has been incorporated into the OWA_UTIL package - you probably already have it in your database. If not, you can use this really old version that might need a tweak or two to work in your database release:

create or replace procedure who_called_me( owner out varchar2,
name out varchar2,
lineno out number,
caller_t out varchar2 )
call_stack varchar2(4096) default dbms_utility.format_call_stack;
n number;
found_stack BOOLEAN default FALSE;
line varchar2(255);
cnt number := 0;
n := instr( call_stack, chr(10) );
exit when ( cnt = 3 or n is NULL or n = 0 );
line := substr( call_stack, 1, n-1 );
call_stack := substr( call_stack, n+1 );
if ( NOT found_stack ) then
if ( line like '%handle%number%name%' ) then
found_stack := TRUE;
end if;
cnt := cnt + 1;
-- cnt = 1 is ME
-- cnt = 2 is MY Caller
-- cnt = 3 is Their Caller
if ( cnt = 3 ) then
lineno := to_number(substr( line, 13, 6 ));
line := substr( line, 21 );
if ( line like 'pr%' ) then
n := length( 'procedure ' );
elsif ( line like 'fun%' ) then
n := length( 'function ' );
elsif ( line like 'package body%' ) then
n := length( 'package body ' );
elsif ( line like 'pack%' ) then
n := length( 'package ' );
elsif ( line like 'anonymous%' ) then
n := length( 'anonymous block ' );
n := null;
end if;
if ( n is not null ) then
caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
caller_t := 'TRIGGER';
end if;

line := substr( line, nvl(n,1) );
n := instr( line, '.' );
owner := ltrim(rtrim(substr( line, 1, n-1 )));
name := ltrim(rtrim(substr( line, n+1 )));
end if;
end if;
end loop;

create or replace function who_am_i return varchar2
l_owner varchar2(30);
l_name varchar2(30);
l_lineno number;
l_type varchar2(30);
who_called_me( l_owner, l_name, l_lineno, l_type );
return l_owner || '.' || l_name;



Anonymous thiggo said....

visit me

Fri Oct 09, 12:36:00 AM EDT  

Blogger Mark Freeman said....

This will tell me the name of the package that called me.

How can I find the name of the procedure or function within a package that called me?

Thu Nov 19, 01:24:00 PM EST  

Blogger Thomas Kyte said....

@Mark Freeman

simple answer: you cannot, you get the top level unit (package name) and line number of that unit.

longer answer: we don't store what function or procedure called you anywhere (and remember with overloading there could be 15 procedures with the same name - the name is not important for LOGGING, the line number is)

from the line number YOU can deduce what the name of the procedure is by a quick query on user_source (do it during ANALYSIS of the log, not during logging itself, would take way too long). You would have to read backwards from that line "parsing" the text until you found the procedure or function header (if one exists, nothing to say that a procedure or function in the package was even the a caller - code in a package can run without being in a function/procedure at all)

Fri Nov 20, 08:25:00 AM EST  

Anonymous Eddy said....

That may not work if the log record is older than the package that originated it (since the code of the package may have changed).

Sun Jun 20, 10:47:00 AM EDT  

Anonymous Anonymous said....

I thought you meant to find out whocalled me on the phone :) but in code you can follow the stach trace:)

Thu Jul 15, 05:07:00 PM EDT  

Anonymous Anonymous said....

This has helped me a lot..


Fri Jul 29, 09:09:00 AM EDT  


<< Home