Currently Executing Procedure Name within the Package

Question

Is there a way to get the currently executing procedure name within the package?

create or replace package test_pkg
as
    procedure proc1;
end test_pkg;

create or replace package body test_pkg
as
    procedure proc1 
    is
        -- // Get the Procedure Name here?? //
    end proc1;
end test_pkg;

Answer 1

In 12c, the current subprogram name is just:

utl_call_stack.subprogram(1)(2);

The current package can also be got from

utl_call_stack.subprogram(1)(1);

but it is generally easier to just use $$plsql_unit. You can also get the qualified name (package.procedure) as:

utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(1));

However, I can't think of any situation where a procedure or function (or object method) would want its own name. This functionality is mostly useful in a logging procedure, in which case the 'who called me?' code should be in the logger, and not repeated in every single thing that calls it. Therefore, I would strongly suggest avoiding any 'who am I?' logic in procedures. Instead, put something like this in your logger (requires 12.1 or later):

create or replace procedure logdemo
as
    k_calling_package constant varchar2(128) := utl_call_stack.subprogram(2)(1);
    k_calling_subprog constant varchar2(128) := utl_call_stack.subprogram(2)(2);
begin
    dbms_output.put_line
    ( $$plsql_unit ||
      ' called from package '||k_calling_package||', subprogram '||k_calling_subprog );
end logdemo;

Unfortunately it's a little more complicated in 11g as you have to parse dbms_utility.format_call_stack, and as this only gives you the package name and line number (in a linefeed-delimited text string), you then have to query all_source to find the subprogram name.

I can post some 11g code if you clarify what it's for. In my 11g logger I found it useful to capture dbms_utility.format_error_backtrace as well as dbms_utility.format_call_stack depending on sqlcode etc, so there is a bunch of logic that is specific to logging, which you may not need if you just want to capture the current procedure name for some other reason.

Answer 2

If it were a standalone procedure (i.e. not the one within the package), you'd get the answer rather easy:

SQL> create or replace procedure p_test is
  2  begin
  3    dbms_output.put_line('I am ' || $$plsql_unit);
  4    null;
  5  end;
  6  /

Procedure created.

SQL> exec p_test
I am P_TEST

PL/SQL procedure successfully completed.

SQL>

---

For packages, things aren't that simple any more. The above option (using $$plsql_unit) returns the package body name, not the procedure's name:

SQL> create or replace package pkg_test as
  2    procedure p_test_in_pkg;
  3  end;
  4  /

Package created.

SQL> create or replace package body pkg_test as
  2    procedure p_test_in_pkg is
  3    begin
  4      dbms_output.put_line('Packaged procedure whose name is ' || $$plsql_unit);
  5      null;
  6    end;
  7  end;
  8  /

Package body created.

SQL> exec pkg_test.p_test_in_pkg;
Packaged procedure whose name is PKG_TEST     --> should have been "p_test_in_pkg"

PL/SQL procedure successfully completed.

The following function seems to be working. The author is garbuya, source is OTN Forum's discussion Efficient WHO_AM_I and WHO_CALLED_ME discussion (dated in 2010).

create or replace
FUNCTION FN_WHO_AM_I ( p_lvl  NUMBER DEFAULT 0) RETURN VARCHAR2
IS
/***********************************************************************************************
FN_WHO_AM_I returns the full ORACLE name of your object including schema and package names
--
FN_WHO_AM_I(0) - returns the name of your object
FN_WHO_AM_I(1) - returns the name of calling object
FN_WHO_AM_I(2) - returns the name of object, who called calling object
etc., etc., etc.... Up to to he highest level
-------------------------------------------------------------------------------------------------
Copyrigth GARBUYA 2010
*************************************************************************************************/
TYPE str_varr_t   IS VARRAY(2) OF CHAR(1);
TYPE str_table_t  IS TABLE OF VARCHAR2(256);
TYPE num_table_t  IS TABLE OF NUMBER;
v_stack           VARCHAR2(2048) DEFAULT UPPER(dbms_utility.format_call_stack);
v_tmp_1           VARCHAR2(1024);
v_tmp_2           VARCHAR2(1024);
v_pkg_name        VARCHAR2(32);
v_obj_type        VARCHAR2(32);
v_owner           VARCHAR2(32);
v_idx             NUMBER := 0;
v_pos1            NUMBER := 0;
v_pos2            NUMBER := 0;
v_line_nbr        NUMBER := 0;
v_blk_cnt         NUMBER := 0;
v_str_len         NUMBER := 0;
v_bgn_cnt         NUMBER := 0;
v_end_cnt         NUMBER := 0;
it_is_comment     BOOLEAN := FALSE;
it_is_literal     BOOLEAN := FALSE;
v_literal_arr     str_varr_t := str_varr_t ('''', '"');
v_blk_bgn_tbl     str_table_t := str_table_t (' IF '   , ' LOOP '   , ' CASE ', ' BEGIN ');
v_tbl             str_table_t := str_table_t();
v_blk_bgn_len_tbl num_table_t := num_table_t();


BEGIN

   v_stack := SUBSTR(v_stack,INSTR(v_stack,CHR(10),INSTR(v_stack,'FN_WHO_AM_I'))+1)||'ORACLE'; -- skip myself

   FOR v_pos2 in 1 .. p_lvl LOOP  -- advance to the input level
      v_pos1 := INSTR(v_stack, CHR(10));
      v_stack := SUBSTR(v_stack, INSTR(v_stack, CHR(10)) + 1);
   END LOOP;

   v_pos1 := INSTR(v_stack, CHR(10));
   IF v_pos1 = 0 THEN
      RETURN (v_stack);
   END IF;

   v_stack := SUBSTR(v_stack, 1, v_pos1 - 1);  -- get only current level
   v_stack := TRIM(SUBSTR(v_stack, instr(v_stack, ' ')));  -- cut object handle
   v_line_nbr := TO_NUMBER(SUBSTR(v_stack, 1, instr(v_stack, ' ') - 1));  -- get line number
   v_stack := TRIM(SUBSTR(v_stack, instr(v_stack, ' ')));  -- cut line number
   v_pos1 := INSTR(v_stack, ' BODY');
   IF v_pos1  = 0 THEN
      RETURN (v_stack);
   END IF;

   v_pos1 := INSTR(v_stack, ' ', v_pos1 + 2);  -- find end of object type
   v_obj_type := SUBSTR(v_stack, 1, v_pos1 - 1);  -- get object type
   v_stack := TRIM(SUBSTR(v_stack, v_pos1 + 1));  -- get package name
   v_pos1 := INSTR(v_stack, '.');
   v_owner := SUBSTR(v_stack, 1, v_pos1 - 1);  -- get owner
   v_pkg_name  := SUBSTR(v_stack, v_pos1 + 1);  -- get package name
   v_blk_cnt := 0;
   it_is_literal := FALSE;
   --
   FOR v_idx in v_blk_bgn_tbl.FIRST .. v_blk_bgn_tbl.LAST
   LOOP
      v_blk_bgn_len_tbl.EXTEND(1);
      v_blk_bgn_len_tbl (v_blk_bgn_len_tbl.last) := LENGTH(v_blk_bgn_tbl(v_idx));
   END LOOP;
   --
   FOR src
   IN ( SELECT ' '||REPLACE(TRANSLATE(UPPER(text), ';('||CHR(10), '   '),'''''',' ') ||' ' text
        FROM all_source
        where owner = v_owner
        and name    = v_pkg_name
        and type    = v_obj_type
        and line    < v_line_nbr
        ORDER  BY line
      )
   LOOP
      v_stack := src.text;
      IF it_is_comment THEN
         v_pos1 :=  INSTR (v_stack, '*/');
         IF v_pos1 > 0 THEN
            v_stack := SUBSTR (v_stack, v_pos1 + 2);
            it_is_comment := FALSE;
         ELSE
            v_stack := ' ';
         END IF;
      END IF;
      --
      IF v_stack != ' ' THEN
      --
         v_pos1 := INSTR (v_stack, '/*');
         WHILE v_pos1 > 0 LOOP
            v_tmp_1 := SUBSTR (v_stack, 1, v_pos1 - 1);
            v_pos2 := INSTR (v_stack, '*/');
            IF v_pos2 > 0 THEN
               v_tmp_2 := SUBSTR (v_stack, v_pos2 + 2);
               v_stack := v_tmp_1||v_tmp_2;
            ELSE
               v_stack := v_tmp_1;
               it_is_comment := TRUE;
            END IF;
            v_pos1 := INSTR (v_stack, '/*');
         END LOOP;
         --
         IF v_stack != ' ' THEN
            v_pos1 := INSTR (v_stack, '--');
            IF v_pos1 > 0 THEN
               v_stack := SUBSTR (v_stack, 1, v_pos1 - 1);
            END IF;
            --
            IF v_stack != ' ' THEN
               FOR v_idx in v_literal_arr.FIRST .. v_literal_arr.LAST
               LOOP
                  v_pos1 := INSTR (v_stack, v_literal_arr (v_idx) );
                  WHILE v_pos1 > 0  LOOP
                     v_pos2 := INSTR (v_stack, v_literal_arr (v_idx), v_pos1 + 1);
                     IF v_pos2 > 0 THEN
                        v_tmp_1 := SUBSTR (v_stack, 1, v_pos1 - 1);
                        v_tmp_2 := SUBSTR (v_stack, v_pos2 + 1);
                        v_stack := v_tmp_1||v_tmp_2;
                     ELSE
                        IF it_is_literal THEN
                           v_stack := SUBSTR (v_stack, v_pos1 + 1);
                           it_is_literal := FALSE;
                        ELSE
                           v_stack := SUBSTR (v_stack, 1, v_pos1 - 1);
                           it_is_literal := TRUE;
                        END IF;
                     END IF;
                     v_pos1 := INSTR (v_stack, v_literal_arr (v_idx) );
                  END LOOP;
               END LOOP;
               --
               IF v_stack != ' ' THEN
                  WHILE INSTR (v_stack, '  ') > 0
                  LOOP
                     v_stack := REPLACE(v_stack, '  ', ' ');
                  END LOOP;
                  v_stack := REPLACE(v_stack, ' END IF ', ' END ');
                  v_stack := REPLACE(v_stack, ' END LOOP ', ' END ');
                  --
                  IF v_stack != ' ' THEN
                     v_stack := ' '||v_stack;
                     v_pos1 := INSTR(v_stack, ' FUNCTION ') + INSTR(v_stack, ' PROCEDURE ');
                     IF v_pos1 > 0 THEN
                        v_obj_type := TRIM(SUBSTR(v_stack, v_pos1 + 1, 9));  -- get object type
                        v_stack := TRIM(SUBSTR(v_stack, v_pos1 + 10))||'  ';  -- cut object type
                        v_stack := SUBSTR(v_stack, 1,  INSTR(v_stack, ' ') - 1 );  -- get object name
                        v_tbl.EXTEND(1);
                        v_tbl (v_tbl.last) := v_obj_type||' '||v_owner||'.'||v_pkg_name||'.'||v_stack;
                     END IF;
                  --
                     v_pos1 := 0;
                     v_pos2 := 0;
                     v_tmp_1 := v_stack;
                     v_tmp_2 := v_stack;
                     FOR v_idx in v_blk_bgn_tbl.FIRST .. v_blk_bgn_tbl.LAST
                     LOOP
                        v_str_len := NVL(LENGTH(v_tmp_1),0);
                        v_tmp_1 := REPLACE(v_tmp_1,v_blk_bgn_tbl(v_idx), NULL);
                        v_bgn_cnt := NVL(LENGTH(v_tmp_1), 0);
                        v_pos1 := v_pos1 + (v_str_len - v_bgn_cnt)/v_blk_bgn_len_tbl(v_idx);
                        v_str_len := NVL(LENGTH(v_tmp_2),0);
                        v_tmp_2 := REPLACE(v_tmp_2,' END ', NULL);
                        v_end_cnt := NVL(LENGTH(v_tmp_2), 0);
                        v_pos2 := v_pos2 + (v_str_len - v_end_cnt)/5; --- 5 is the length(' END ') 
                     END LOOP;
                     IF v_pos1 > v_pos2 THEN
                        v_blk_cnt := v_blk_cnt + 1;
                     ELSIF v_pos1 < v_pos2 THEN
                        v_blk_cnt := v_blk_cnt - 1;
                        IF v_blk_cnt = 0 AND v_tbl.COUNT > 0 THEN
                           v_tbl.DELETE(v_tbl.last);
                        END IF;
                     END IF;
                  END IF;
               END IF;
            END IF;
         END IF;
      END IF;
   END LOOP;

   RETURN CASE v_tbl.COUNT WHEN 0 THEN 'UNKNOWN' ELSE v_tbl(v_tbl.LAST) END;

END;
/

Testing:

SQL> create or replace package body pkg_test as
  2    procedure p_test_in_pkg is
  3    begin
  4      dbms_output.put_line('Packaged procedure, using $$plsql_unit = ' || $$plsql_unit);
  5      dbms_output.put_line('FN_WHO_AM_I = ' || fn_who_am_i);
  6      null;
  7    end;
  8  end;
  9  /

Package body created.

SQL> exec pkg_test.p_test_in_pkg;
Packaged procedure, using $$plsql_unit = PKG_TEST        --> this one is wrong (package body name) ...
FN_WHO_AM_I = PROCEDURE SCOTT.PKG_TEST.P_TEST_IN_PKG     --> ... but this one is OK

PL/SQL procedure successfully completed.

SQL>

Answer 3

An approach using the idea borrowed from here : http://www.orafaq.com/forum/t/173023/

Note : This works fine only in Oracle 12c. For 11g, It only gives Package name.

Create a procedure called show_caller. This uses the OWA_UTIL.WHO_CALLED_ME procedure.

create or replace procedure show_caller
as
   l_owner  varchar2(200);
   l_name   varchar2(200); 
   l_lineno number;
   l_caller varchar2(200);
begin 
   OWA_UTIL.WHO_CALLED_ME (l_owner, l_name,l_lineno,l_caller);
   dbms_output.put_line('Inside '||l_name);

end;
/

Now, you can write your package as:

CREATE OR replace PACKAGE test_pkg 
AS 
  PROCEDURE proc1; 
END test_pkg; 

/ 
CREATE OR replace PACKAGE BODY test_pkg 
AS 
  PROCEDURE Proc1 
  AS 
  BEGIN 
      show_caller;  -- proc1 calls show_caller 
  END proc1; 
END test_pkg; 

/ 

Execution.

SET SERVEROUTPUT ON
BEGIN
   Test_pkg.Proc1;
END;
/
Inside TEST_PKG.PROC1

Note that this will print the name of the procedure.If you want to use it as a variable, pass l_name(along with others if needed) as OUT variable from show_caller

Live SQL Demo ( Free OTN account required )

One other option is to use OWA_UTIL.GET_PROCEDURE function within the procedure: but it doesn't seem work for me in this context. I would be glad to know more about this from experts.