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.
LVL 2
mccallwAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.