orangewise (nerdy stuff)

Found this handy piece of code that tells you who is calling a procedure. This can be used when you write a package for debugging. Create the following procedure:

create or replace procedure who_called_me(
   owner      out varchar2
  ,name       out varchar2
  ,lineno     out number
  ,caller_t   out varchar2
) is
  call_stack  varchar2(4096)
    default dbms_utility.format_call_stack;
  n           number;
  found_stack BOOLEAN default FALSE;
  line        varchar2(255);
  cnt         number := 0;
begin
  loop
    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;
    else
      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 ' );
        else
          n := length( 'anonymous block ' );
        end if;
        caller_t :=
          ltrim(rtrim(upper(substr( line, 1, n-1 ))));
        line := substr( line, n );
        n := instr( line, '.' );
        owner := ltrim(rtrim(substr( line, 1, n-1 )));
        name  := ltrim(rtrim(substr( line, n+1 )));
      end if;
    end if;
  end loop;
end who_called_me;
/

Procedure created.

Small example on how you can use it, create 2 procedures:

create or replace procedure called
is
  l_owner varchar2(30);
  l_name varchar2(30);
  l_lineno number;
  l_caller_t varchar2(2000);
begin
  dbms_output.put_line('starting procedure called');
  who_called_me(l_owner, l_name, l_lineno, l_caller_t );
  dbms_output.put_line('called by '|| l_owner
    ||'.'||l_name||' line '||l_lineno
    ||' ('||l_caller_t||')');
  dbms_output.put_line('the end...');
end called;
/

Procedure created.

create or replace procedure caller
is
begin
  called;
end caller;
/
Procedure created.

Then runnit:

exec caller
starting procedure called
called by APPS.CALLER line 4 (PROCEDURE)
the end...

PL/SQL procedure successfully completed.