PL/SQL Who am I function similar to T-SQL's OBJECT_NAME(@@PROCID)

Question

In T-SQL, the following command will return the name of the current running stored procedure:

OBJECT_NAME(@@PROCID) 

In PL/SQL, when I place the following code on a stored procedure of a package, it returns the name of the parent package rather than the executing stored procedure.

$$PLSQL_UNIT

Is there a way to get the name of the executing proceure in PL/SQL?

Answer 1

Yes, it's impossible in versions prior to 12. However you can try some hacks.

1. V$SESSION view has PLSQL_ENTRY_SUBPROGRAM_ID and PLSQL_SUBPROGRAM_ID fields which can lead you to currently executing procedure.

Current session is:

select PLSQL_ENTRY_OBJECT_ID,
       PLSQL_ENTRY_SUBPROGRAM_ID,
       PLSQL_OBJECT_ID,
       PLSQL_SUBPROGRAM_ID
from V$SESSION
where AUDSID = sys_context( 'userenv', 'sessionid' )

And then find a procedure name by querying a view ALL_PROCEDURES:

select PROCEDURE_NAME
from ALL_PROCEDURES
where OBJECT_ID = :objectId
  and SUBPROGRAM_ID = :subprogramId

This view contains functions and procedures declared in packages but doesn't contain those which are declared in package bodies.

2. dbms_utility.format_call_stack shows line number and source name. Parsed output can be gained by owa_util.who_called_me. The raw output also contains object handle which can give you access to source code of anonymous block.

dbms_utility.format_call_stack sample output:

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
B87FEF1C         1  anonymous block

And then:

select SQL_FULLTEXT from V$SQL where CHILD_ADDRESS = 'B87FEF1C'

The source code of stored procedures can be gained from ALL_SOURCE.

3. Once you have a source code of calling method and a line number in that code, you can parse it to obtain procedure name.

The only bad example is one-liners. But it's a rare situation.

procedure outer is procedure inner is begin whoami; end; begin whoami; end;

You have information that whoami was called on this line. But you don't know if it's the first occurrence or the second one. So, even parsing the source code cannot lead you to the exact solution.

Other situations can be processed by parsing the source code. Here's my attempt. Sample usage:

create package APCKG is
  procedure PROC;
end;
/
create package body APCKG is
  procedure PROC is
    procedure "INNER/proc" is
    begin
      dbms_output.put_line( p_stack.whoAmI );
    end;
  begin
    "INNER/proc";
  end;
end;
/
begin
  APCKG.PROC;
end;

Output:

5: YOUR_SCHEMA.PACKAGE BODY APCKG.PROCEDURE PROC.PROCEDURE "INNER/proc"

Output format:

Line number + ': ' + Owner + [ '.' + Type + ' ' + Name ]*

So it returns only the last caller and its hierarchy. One procedure inside another one which is inside a function which is located in a package body.

Use utl_call_stack if you need an exact solution and have Oracle 12. This solution is for prior versions (tested on 10.2). It will return the first occurrence for one-liner example above.

UPD:

I've upgraded this solution to a full-scale package for call stack control. Benefits:

• Tested on Oracle 9 (separate package version), 10 and 11.
• It really parses the source (i.e. lexical analysis, tokenization etc.).
• Pure PL/SQL.
• Parses anonymous blocks source code (they can contain inner procedures too).
• Contains methods similar to utl_call_stack.
• Methods whoAmI and whoCalledMe.
• Supports double quoted names.
• Supports q-notation for strings.
• Skips multiline and one-line comments.
• Skips procedure and function declarations without definitions.

Source code.

UPD 2:

• Added support for conditional compilation.

UPD 3:

• Implemented a backport of Oracle 12 package utl_call_stack for versions 9, 10 and 11.