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:
- The information it returns is parsed from the output of
DBMS_UTILITY.FORMAT_CALL_STACK. - 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:
Related articles:
- Here’s a Quick Way to Get the Line Number in PL/SQL
- How to find where an error was raised in PL/SQL
- Two Oracle PL/SQL Features You Probably Don’t Know About
there is a good reason it looks alot like my code….
owa_opt_lock was originally mine – many of the owa_* stuff in owa_util was, from 1995 way back when…
Your comment screen is really hard to use, the tab key doesn’t take you from field to field naturally (you almost have to mouse) and the field icons take a bit of thinking to figure out what they are (no ALT tags?? took me a while to figure out the first one must be my name and the second must be my email address…)
Hi Tom,
OWA_UTIL is an old package indeed. In fact, by looking at the source code I see that its creation date was 07/09/95.
I appreciate your feedback regarding the comment form. All icon image tags do have alt attributes. But, I had to add the title attribute to make it work both in IE and Firefox. So, now if you hover your mouse over an icon, you should see a tooltip popup.
In any case, I have added text next to each field to make it clearer. I have also fixed the tab order of the fields.
I hope that will make it easier for you to comment in the future — Thanks.
Very nice – thanks Eddie – much more “clear” now (and tabbing works
Why not just EXEC my_proc; ?
Sure, why not. They are basically the same to me:
The problem with this approach is that it tells me what package called my procedure but not which procedure or function inside that package. This information is not included in the stack. Any help here?
If I use this in a member procedure of an object, I don’t get the procedure name. I get the object name. Tom Kyte’s link (who_am_i) also does not give the name of the procedure. How do I get this informatopn?