Thanks for the question, fabian.

Asked: February 29, 2012 - 4:35 am UTC

Last updated: May 29, 2019 - 6:28 am UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

Is there a way to know who called procedure or function? For example I have procedure

procedure fn_is_called is
begin
dbms_output.put_line('it was called by '||MAGIC_FROM_ASKTOM);
end;

and

procedure fn_is_calling is
begin
fn_is_called;
end;

and when I execute;

SQL> exec fn_is_calling;

I would like to get:

it was called by fn_is_calling

and Tom said...

check out

owa_util.who_called_me

a supplied package.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

thanks

fabian, February 29, 2012 - 5:41 am UTC

exactly what I was looking for.
thank you very much.
Regards,

works well

Stefano Cinquegranelli, February 29, 2012 - 6:10 am UTC

Very useful. Caller_type could be functions, procedures, anonymous blocks. May I post an example ?

CREATE OR REPLACE PROCEDURE a_proc
IS
owner_name VARCHAR2 (100);
caller_name VARCHAR2 (100);
line_number NUMBER;
caller_type VARCHAR2 (100);
BEGIN
OWA_UTIL.WHO_CALLED_ME (owner_name,caller_name,line_number,caller_type);
DBMS_OUTPUT.put_line ( caller_type
|| ' '
|| owner_name
|| '.'
|| caller_name
|| ' called A_PROC from line number '
|| line_number
);
END;
/

Doesn't work perfectly in nested procedures/functions

Arindam, May 28, 2019 - 8:44 am UTC

While this works all usual cases, it doesn't if called form within a nested procedures/functions. In that case, it considers the outermost object only.

Is there some other alternative which can come to rescue to the complicated case if called from a nested procedures/functions?

May 29, 2019 - 6:28 am UTC

If you're on 12 and above, see if UTL_CALL_STACK helps

SQL> create or replace package PKG is
  2    procedure p;
  3    procedure p1;
  4    procedure p2;
  5    procedure p3;
  6  end;
  7  /

Package created.

SQL> create or replace package body PKG is
  2
  3  procedure p is
       ...
 19
 20  exception
 21    when others then
 22      for i in 1 .. utl_call_stack.dynamic_depth loop
 23        dbms_output.put_line(
 24          utl_call_stack.unit_line(i)||'-'||
 25          utl_call_stack.concatenate_subprogram(
 26             utl_call_stack.subprogram(i))
 27          );
 28      end loop;
 29      raise;
 30  end;
 31
 32  procedure p1 is begin p; end;
 33  procedure p2 is begin p1; end;
 34  procedure p3 is begin p2; end;
 35
 36  end;
 37  /

Package body created.

SQL> exec pkg.p3

23-PKG.P
32-PKG.P1
33-PKG.P2
34-PKG.P3
1-__anonymous_block

BEGIN pkg.p3; END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "SCOTT.PKG", line 29
ORA-06512: at "SCOTT.PKG", line 32
ORA-06512: at "SCOTT.PKG", line 33
ORA-06512: at "SCOTT.PKG", line 34
ORA-06512: at line 1

Notice you get all the subprograms, not just the package name as you get with FORMAT_CALL_STACK

Breadcrumb

Question and Answer