Does anyone know whether it's possible for a PL/SQL procedure (an error-logging one in this case) to get the name of the function/procedure which called it?
Obviously I could pass the name in as a parameter, but it'd be nice to make a system call or something to get the info - it could just return null or something if it wasn't called from a procedure/function.
If there's no method for this that's fine - just curious if it's possible (searches yield nothing).
There is a package called OWA_UTIL (which is not installed by default in older versions of the database). This has a method WHO_CALLED_ME() which returns the OWNER, OBJECT_NAME, LINE_NO and CALLER_TYPE. Note that if the caller is a packaged procedure it will return the PACKAGE name not the procedure name. In this case there is no way of getting the procedure name; this is because the procedure name can be overloaded, so it's not necessarily very useful.
Since 10gR2 there is also the $$PLSQL_UNIT special function; this will also return the OBJECT NAME (i.e. package not packaged procedure).
I found this forum: http://www.orafaq.com/forum/t/60583/0/. It may be what you are looking.
Basically, you can use the Oracle supplied dbms_utility.format_call_stack:
scott@ORA92> CREATE TABLE error_tab
2 (who_am_i VARCHAR2(61),
3 who_called_me VARCHAR2(61),
4 call_stack CLOB)
5 /
Table created.
scott@ORA92>
scott@ORA92> CREATE OR REPLACE PROCEDURE d
2 AS
3 v_num NUMBER;
4 v_owner VARCHAR2(30);
5 v_name VARCHAR2(30);
6 v_line NUMBER;
7 v_caller_t VARCHAR2(100);
8 BEGIN
9 select to_number('a') into v_num from dual; -- cause error for testing
10 EXCEPTION
11 WHEN OTHERS THEN
12 who_called_me (v_owner, v_name, v_line, v_caller_t);
13 INSERT INTO error_tab
14 VALUES (who_am_i,
15 v_owner || '.' || v_name,
16 dbms_utility.format_call_stack);
17 END d;
18 /
Procedure created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> CREATE OR REPLACE PROCEDURE c
2 AS
3 BEGIN
4 d;
5 END c;
6 /
Procedure created.
scott@ORA92> CREATE OR REPLACE PROCEDURE b
2 AS
3 BEGIN
4 c;
5 END b;
6 /
Procedure created.
scott@ORA92> CREATE OR REPLACE PROCEDURE a
2 AS
3 BEGIN
4 b;
5 END a;
6 /
Procedure created.
scott@ORA92> execute a
PL/SQL procedure successfully completed.
scott@ORA92> COLUMN who_am_i FORMAT A13
scott@ORA92> COLUMN who_called_me FORMAT A13
scott@ORA92> COLUMN call_stack FORMAT A45
scott@ORA92> SELECT * FROM error_tab
2 /
WHO_AM_I WHO_CALLED_ME CALL_STACK
------------- ------------- ---------------------------------------------
SCOTT.D SCOTT.C ----- PL/SQL Call Stack -----
object line object
handle number name
6623F488 1 anonymous block
66292138 13 procedure SCOTT.D
66299430 4 procedure SCOTT.C
6623D2F8 4 procedure SCOTT.B
6624F994 4 procedure SCOTT.A
66299984 1 anonymous block
scott@ORA92>
Basically, all you need to do is to define vars and pass them in a call to a utility method to fill them up with values:
create or replace procedure some_test_proc (p_some_int int)
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);
-- now you can insert those values along with systimestamp into a log file
....
end;
