• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1449
  • Last Modified:

PL/SQL - Determining which procedure called current procedure

Using PL/SQL, is there a way within a current procedure to determine which procedure called it.  

Also, is there a way to determine within the current procedure to return the name of the current procedure.

So, in summary I need to know a method to return the name of the calling procedure and the current procedure.
0
mccallw
Asked:
mccallw
1 Solution
 
mandarexpertCommented:
For to get the procedure name and calling procedure name we have to take care while coding itself.Like u can make the string dynamically and display it on the screen with the help of DBMS_OUTPUT.PUT_LINE(string).
Or if that procedure is getting called from Front-End we can put the dynamic string in one of the temp.table. so if u select from that temp table we can get which procedure is currently running.
0
 
mccallwAuthor Commented:
Thanks for the response mandarexpert, but both of your options are workarounds that we cannot use.  We would like to somehow using an existing package that can determine the name and calling name and use it for logic rather than just a return to the screen.

The temp table idea may be interesting.  Are you suggesting to change the code of all procedures to update a temp table with it's own name prior to running or calling another procedure?
0
 
syakobsonCommented:
You could use DBMS_UTILITY.FORMAT_CALL_STACK and call it in every procedure you want to trace. This way you will see complete execution chain:

SQL> set serveroutput on
SQL> CREATE OR REPLACE
  2  PROCEDURE P1
  3   IS
  4   BEGIN
  5   DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
  6  END;
  7  /

Procedure created.

SQL> CREATE OR REPLACE
  2  PROCEDURE P2
  3   IS
  4   BEGIN
  5   P1;
  6  END;
  7  /

Procedure created.

SQL> CREATE OR REPLACE
  2  PROCEDURE P3
  3   IS
  4   BEGIN
  5   P2;
  6  END;
  7  /

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> EXEC P3
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
 28d3dc0         4
procedure SYSTEM.P1
 28b21e4         4  procedure SYSTEM.P2
 24b3e48         4  procedure SYSTEM.P3

2432e2c         1  anonymous block


PL/SQL procedure successfully completed.

SQL> EXEC P1
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
 28d3dc0         4
procedure SYSTEM.P1
 242a49c         1  anonymous block


PL/SQL procedure successfully completed.

SQL>


Or you can write a package based on DBMS_UTILITY.FORMAT_CALL_STACK to provide info you desire. Below is an example of such package I found in my archives:

SQL> CREATE OR REPLACE
  2  PACKAGE CallStack
  3   AS
  4    TYPE CallStackRecordType IS RECORD(ObjectHandle VARCHAR2(2000),
  5                                       ObjectType   VARCHAR2(2000),
  6                                       ObjectName   VARCHAR2(2000),
  7                                       LineNumber   NUMBER
  8                                      );
  9    TYPE CallStackTableType IS TABLE OF CallStackRecordType
 10          INDEX BY BINARY_INTEGER;
 11    CallStackTable CallStackTableType;
 12    PROCEDURE Get;
 13  END CallStack;
 14  /

Package created.

SQL> CREATE OR REPLACE
  2  PACKAGE BODY CallStack
  3   AS
  4    PROCEDURE Get
  5     IS
  6          Stack           VARCHAR2(2000);
  7          Temp            VARCHAR2(2000);
  8          C               VARCHAR2(1);
  9          I               BINARY_INTEGER;
 10          SpacePosition   NUMBER;
 11     BEGIN
 12          CallStackTable.DELETE;
 13          Stack := RTRIM(DBMS_UTILITY.FORMAT_CALL_STACK);
 14          Stack := REPLACE(REPLACE(Stack,CHR(10),'|'),CHR(13),'|');
 15          Stack := REPLACE(Stack,'||','|');
 16          Stack := LTRIM(SUBSTR(Stack,INSTR(Stack,'|',1,3) + 1)) || '| ';
 17          I := 0;
 18          WHILE LENGTH(Stack) != 1 LOOP
 19            Stack := LTRIM(Stack);
 20            SpacePosition := INSTR(Stack,' ');
 21            CallStackTable(I).ObjectHandle := SUBSTR(Stack,1,SpacePosition - 1);
 22            Stack := LTRIM(SUBSTR(Stack,SpacePosition));
 23            SpacePosition := INSTR(Stack,' ');
 24            CallStackTable(I).LineNumber := SUBSTR(Stack,1,SpacePosition - 1);
 25            Stack := LTRIM(SUBSTR(Stack,SpacePosition));
 26            SpacePosition := INSTR(Stack,'|');
 27            Temp := SUBSTR(Stack,1,SpacePosition - 1);
 28            IF Temp = 'anonymous block'
 29              THEN
 30                CallStackTable(I).ObjectName := NULL;
 31                CallStackTable(I).ObjectType := 'anonymous block';
 32              ELSE
 33                CallStackTable(I).ObjectName := LTRIM(SUBSTR(Temp,INSTR(Temp,' ', -1)+1));
 34                CallStackTable(I).ObjectType := LTRIM(SUBSTR(Temp,1,INSTR(Temp,' ', -1) - 1));
 35            END IF;
 36            Stack := SUBSTR(Stack,SpacePosition + 1);
 37            I := I + 1;
 38          END LOOP;
 39          CallStackTable.DELETE(0);
 40     END Get;
 41  END CallStack;
 42  /

Package body created.

SQL>
SQL> CREATE OR REPLACE
  2  PROCEDURE P1
  3   IS
  4   BEGIN
  5          CallStack.Get;
  6          DBMS_OUTPUT.PUT_LINE('Current module is ' || CallStack.CallStackTable(1).ObjectType ||
  7                               ' ' || CallStack.CallStackTable(1).ObjectName);
  8          IF CallStack.CallStackTable.COUNT = 1
  9            THEN
 10              DBMS_OUTPUT.PUT_LINE('It is top level module.');
 11            ELSE
 12              DBMS_OUTPUT.PUT_LINE('Calling module is ' || CallStack.CallStackTable(2).ObjectType
 13                                   ' ' || CallStack.CallStackTable(2).ObjectName);
 14          END IF;
 15  END;
 16  /

Procedure created.

SQL> CREATE OR REPLACE
  2  PROCEDURE P2
  3   IS
  4   BEGIN
  5          P1;
  6  END;
  7  /

Procedure created.

SQL> CREATE OR REPLACE
  2  PROCEDURE P3
  3   IS
  4   BEGIN
  5          P2;
  6  END;
  7  /

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> EXEC P3
Current module is procedure SYSTEM.P1
Calling module is procedure SYSTEM.P2

PL/SQL procedure successfully completed.

SQL> EXEC P1
Current module is procedure SYSTEM.P1
Calling module is anonymous block

PL/SQL procedure successfully completed.

SQL>

Solomon Yakobson.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
mccallwAuthor Commented:
Thanks for the complete and thorough answer!  I will give these options a try.
0
 
jaramillCommented:
Here's a quick and dirty way to at least NARROW down the possibilities of what procedure/function called a procedure or function.

If you use TOAD, in the Schema Browser, there is a tab on the left side for Procedures/Functions/Packages called "PROCS".  When you have chosen the callee procedure you're interested in, then on the right hand side, there's a tab called "Deps (Used By)" meaning Dependencies (aka what procedures/functions use this callee).

When you click on this tab it will list the potential caller objects.  Not as thorough an answer as Solomon's but it's quick.

Gio
0
 
jaramillCommented:
Here's a quick and dirty way to at least NARROW down the possibilities of what procedure/function called a procedure or function.

If you use the TOAD PL/SQL editor in the Schema Browser, there is a tab on the left side for Procedures/Functions/Packages called "PROCS".  When you have chosen the callee procedure you're interested in, then on the right hand side, there's a tab called "Deps (Used By)" meaning Dependencies (aka what procedures/functions use this callee).

When you click on this tab it will list the potential caller objects.  Not as thorough an answer as Solomon's but it's quick.

Gio
0
 
mccallwAuthor Commented:
Thanks jaramill.  I use TOAD as well and have seen this function.

I am really interested in getting this information during the execution of the procedure itself, however, so that I can perform logic based on the calling procedure.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now