Did you call me?

The Oracle supplied PL/SQL package OWA_UTIL has a very handy procedure called WHO_CALLED_ME. This procedure returns information (in the form of output parameters) about the PL/SQL code unit that invoked it. For example:

SQL> CREATE OR REPLACE PROCEDURE my_proc
  2  IS
  3     owner_name    VARCHAR2 (100);
  4     caller_name   VARCHAR2 (100);
  5     line_number   NUMBER;
  6     caller_type   VARCHAR2 (100);
  7  BEGIN
  8     OWA_UTIL.WHO_CALLED_ME (owner_name,caller_name,line_number,caller_type);
  9     DBMS_OUTPUT.put_line (   caller_type
 10                           || ' '
 11                           || owner_name
 12                           || '.'
 13                           || caller_name
 14                           || ' called MY_PROC from line number '
 15                           || line_number
 16                          );
 17  END;
 18  /

Procedure created.

Calling my_proc from an anonymous block:

SQL> SET serveroutput on   

SQL> BEGIN
  2     my_proc;
  3  END;
  4  /
ANONYMOUS BLOCK . called MY_PROC from line number 2

PL/SQL procedure successfully completed.

Calling my_proc from a procedure:

SQL> CREATE OR REPLACE PROCEDURE my_proc_2
  2  IS
  3  BEGIN
  4     my_proc;
  5  END;
  6  /

Procedure created.

SQL> BEGIN
  2     my_proc_2;
  3  END;
  4  /
PROCEDURE HR.MY_PROC_2 called MY_PROC from line number 4

PL/SQL procedure successfully completed.

Calling my_proc from a function:

SQL> CREATE OR REPLACE FUNCTION my_func
  2     RETURN VARCHAR2
  3  IS
  4  BEGIN
  5     my_proc;
  6     RETURN 'hi';
  7  END;
  8  /

Function created.

SQL> SELECT my_func
  2    FROM DUAL;

MY_FUNC
----------------------------------------------------------

hi

FUNCTION HR.MY_FUNC called MY_PROC from line number 5

If you look at the source code of the SYS.OWA_UTIL.WHO_CALLED_ME procedure, you will notice two things:

1. The information it returns is parsed from the output of DBMS_UTILITY.FORMAT_CALL_STACK.
2. The source code is almost the same as Tom Kyte’s who_called_me routine.

Even though OWA_UTIL.WHO_CALLED_ME has been around for a long time, I did not know of its existence until Dan Morgan brought it to my attention during the NWOUG 2006 Fall Conference.

Sources and resources:

• psoug.org
• OWA_UTIL.WHO_CALLED_ME Documentation
• DBMS_UTILITY.FORMAT_CALL_STACK Documentation