PL / SQL Who am I like T-SQL OBJECT_NAME (@@ PROCID)

Question

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

OBJECT_NAME(@@PROCID) 

In PL / SQL, when I put the following code in a package stored procedure, it returns the name of the parent package, not the executable stored procedure.

 $$PLSQL_UNIT 

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

Answer 1

Yes, this is not possible in versions prior to 12. However, you can try some hacks.

• V$SESSION view has fields PLSQL_ENTRY_SUBPROGRAM_ID and PLSQL_SUBPROGRAM_ID , which can lead you to the execution of the current procedure.

Current Session:

 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 the name of the procedure by 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 does not contain those declared in package bodies.

2. dbms_utility.format_call_stack shows the line number and source name. Developed results can be obtained using owa_util.who_called_me . The raw output also contains an object handle that can give you access to the source code of an 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 for stored procedures can be obtained from ALL_SOURCE .

3. Once you have the source code of the invocation method and the line number in this code, you can parse it to get the name of the procedure.

The only bad example is single-line. But this is 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 do not know whether it is the first or the second. Thus, even analysis of the source code cannot lead you to an exact solution.

Other situations can be handled by analyzing the source code. Here is my attempt. Using an example:

 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; 

Conclusion:

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

Output format:

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

Thus, it returns only the last caller and its hierarchy. One procedure is inside another, which is inside a function that is in the package body.

Use utl_call_stack if you need an exact solution and you have Oracle 12. This is a solution for previous versions (tested on 10.2). It will return the first occurrence for a single-layer example.

UPD:

I upgraded this solution to a full-blown call stack management package. Benefits:

• Tested on Oracle 9 ( standalone version of the package ), 10 and 11.
• It really analyzes the source (e.g. lexical analysis, tokenization, etc.).
• Pure PL / SQL.
• Parses an anonymous block of source texts (they may also contain internal procedures).
• Contains methods similar to utl_call_stack .
• The whoami and whoCalledMe .
• Supports double-quoted names.
• Supports q -notation for strings.
• Skips multi-line and single-line comments.
• Skips descriptions of procedures and functions without definitions.

Source code .

UPD 2:

• Added support for conditional compilation.

UPD 3:

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

Answer 2

just define a constant with the name of the procedure in the DECLARE section

 whoami CONSTANT VARCHAR2(100) := 'MY_PROCEDURE';