oracle - 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.