Get the name of the calling procedure or function in Oracle PL/SQL

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.

Find out more.

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 ERRORSNo 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 aPL/SQL procedure successfully completed.scott@ORA92> COLUMN who_am_i FORMAT A13scott@ORA92> COLUMN who_called_me FORMAT A13scott@ORA92> COLUMN call_stack    FORMAT A45scott@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 blockscott@ORA92>