Home » SQL & PL/SQL » SQL & PL/SQL » Procedure execution hierarchy
icon6.gif  Procedure execution hierarchy [message #163655] Fri, 17 March 2006 13:56 Go to next message
pkirangi
Messages: 74
Registered: August 2005
Member
Hello All,
I have an issue, wherein I need to find out procedure execution hierarchy.

Lets say Proc A call Proc B
Proc B call Proc C
Proc C call Proc D

In Proc D I encounter an exception, and I need to insert into a error table the hierarchy of the procedure calls, in the error message.
i.e I want to say "Encountered error in D: execution flow Is A.B.C.D"

How can I generate "A.B.C.D", without having to write extra code. Is there a V$table or some internal table which I can use to figure the procedure execution hierarchy?

Thanks
PHK

Report message to a moderator

Re: Procedure execution hierarchy [message #163665 is a reply to message #163655] Fri, 17 March 2006 15:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
You can use the Oracle supplied dbms_utility.foramt_call_stack and/or Tom Kyte's who_am_i and who_called_me available here:

http://asktom.oracle.com/~tkyte/who_called_me/index.html

I have incorporated all three in the demonstration below.

scott@ORA92> CREATE TABLE error_tab
  2    (who_am_i      VARCHAR2(61),
  3  	who_called_me VARCHAR2(61),
  4  	call_stack    CLOB)
  5  /

Table created.

scott@ORA92> 
scott@ORA92> CREATE OR REPLACE PROCEDURE d
  2  AS
  3    v_num	  NUMBER;
  4    v_owner	  VARCHAR2(30);
  5    v_name	  VARCHAR2(30);
  6    v_line	  NUMBER;
  7    v_caller_t VARCHAR2(100);
  8  BEGIN
  9    select to_number('a') into v_num from dual; -- cause error for testing
 10  EXCEPTION
 11    WHEN OTHERS THEN
 12  	 who_called_me (v_owner, v_name, v_line, v_caller_t);
 13  	 INSERT INTO error_tab
 14  	 VALUES (who_am_i,
 15  		 v_owner || '.' || v_name,
 16  		 dbms_utility.format_call_stack);
 17  END d;
 18  /

Procedure created.

scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> CREATE OR REPLACE PROCEDURE c
  2  AS
  3  BEGIN
  4    d;
  5  END c;
  6  /

Procedure created.

scott@ORA92> CREATE OR REPLACE PROCEDURE b
  2  AS
  3  BEGIN
  4    c;
  5  END b;
  6  /

Procedure created.

scott@ORA92> CREATE OR REPLACE PROCEDURE a
  2  AS
  3  BEGIN
  4    b;
  5  END a;
  6  /

Procedure created.

scott@ORA92> execute a

PL/SQL procedure successfully completed.

scott@ORA92> COLUMN who_am_i FORMAT A13
scott@ORA92> COLUMN who_called_me FORMAT A13
scott@ORA92> COLUMN call_stack	  FORMAT A45
scott@ORA92> SELECT * FROM error_tab
  2  /

WHO_AM_I      WHO_CALLED_ME CALL_STACK
------------- ------------- ---------------------------------------------
SCOTT.D       SCOTT.C       ----- PL/SQL Call Stack -----
                              object      line  object
                              handle    number  name
                            6623F488         1  anonymous block
                            66292138        13  procedure SCOTT.D
                            66299430         4  procedure SCOTT.C
                            6623D2F8         4  procedure SCOTT.B
                            6624F994         4  procedure SCOTT.A
                            66299984         1  anonymous block


scott@ORA92>

Report message to a moderator

Re: Procedure execution hierarchy [message #163671 is a reply to message #163655] Fri, 17 March 2006 15:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
I don't know how important the format is or isn't to you. I made a few quick modifications to Tom Kyte's who_called_me procedure to create a flow function that returns something a little closer to what you asked for, and included it in the revised demonstration below. 

scott@ORA92> CREATE TABLE error_tab
  2    (who_am_i      VARCHAR2(61),
  3  	who_called_me VARCHAR2(61),
  4  	flow	      VARCHAR2(2000),
  5  	call_stack    CLOB)
  6  /

Table created.

scott@ORA92> create or replace function flow
  2  	 return      varchar2
  3  as
  4  	 call_stack  varchar2(4096) default dbms_utility.format_call_stack;
  5  	 n	     number;
  6  	 found_stack BOOLEAN default FALSE;
  7  	 line	     varchar2(255);
  8  	 cnt	     number := 0;
  9  	 v_flow      varchar2(2000);
 10  	 caller_t    varchar2(30);
 11  	 owner	     varchar2(30);
 12  	 name	     varchar2(30);
 13  	 lineno      number;
 14  begin
 15  --
 16  	 loop
 17  	     n := instr( call_stack, chr(10) );
 18  	     exit when ( n is NULL or n = 0 );
 19  --
 20  	     line := substr( call_stack, 1, n-1 );
 21  	     call_stack := substr( call_stack, n+1 );
 22  --
 23  	     if ( NOT found_stack ) then
 24  		 if ( line like '%handle%number%name%' ) then
 25  		     found_stack := TRUE;
 26  		 end if;
 27  	     else
 28  		 cnt := cnt + 1;
 29  		 -- cnt = 1 is ME
 30  		 -- cnt = 2 is MY Caller
 31  		 -- cnt = 3 is Their Caller
 32  		 if ( cnt >= 2 ) then
 33  		     lineno := to_number(substr( line, 13, 6 ));
 34  		     line   := substr( line, 21 );
 35  		     if ( line like 'pr%' ) then
 36  			 n := length( 'procedure ' );
 37  		     elsif ( line like 'fun%' ) then
 38  			 n := length( 'function ' );
 39  		     elsif ( line like 'package body%' ) then
 40  			 n := length( 'package body ' );
 41  		     elsif ( line like 'pack%' ) then
 42  			 n := length( 'package ' );
 43  		     elsif ( line like 'anonymous%' ) then
 44  			 n := length( 'anonymous block ' );
 45  		     else
 46  			 n := null;
 47  		     end if;
 48  		     if ( n is not null ) then
 49  			caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
 50  		     else
 51  			caller_t := 'TRIGGER';
 52  		     end if;
 53  
 54  		     line := substr( line, nvl(n,1) );
 55  		     n := instr( line, '.' );
 56  		     owner := ltrim(rtrim(substr( line, 1, n-1 )));
 57  		     name  := ltrim(rtrim(substr( line, n+1 )));
 58  		     v_flow := v_flow || '->' || caller_t || ' ' || owner || '.' || name;
 59  		 end if;
 60  	     end if;
 61  	 end loop;
 62  	 return ltrim (v_flow, '->');
 63  end;
 64  /

Function created.

scott@ORA92> show errors
No errors.
scott@ORA92> 
scott@ORA92> CREATE OR REPLACE PROCEDURE d
  2  AS
  3    v_num	  NUMBER;
  4    v_owner	  VARCHAR2(30);
  5    v_name	  VARCHAR2(30);
  6    v_line	  NUMBER;
  7    v_caller_t VARCHAR2(100);
  8  BEGIN
  9    select to_number('a') into v_num from dual; -- cause error for testing
 10  EXCEPTION
 11    WHEN OTHERS THEN
 12  	 who_called_me (v_owner, v_name, v_line, v_caller_t);
 13  	 INSERT INTO error_tab
 14  	 VALUES (who_am_i,
 15  		 v_owner || '.' || v_name,
 16  		 flow,
 17  		 dbms_utility.format_call_stack);
 18  END d;
 19  /

Procedure created.

scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> CREATE OR REPLACE PROCEDURE c
  2  AS
  3  BEGIN
  4    d;
  5  END c;
  6  /

Procedure created.

scott@ORA92> CREATE OR REPLACE PROCEDURE b
  2  AS
  3  BEGIN
  4    c;
  5  END b;
  6  /

Procedure created.

scott@ORA92> CREATE OR REPLACE PROCEDURE a
  2  AS
  3  BEGIN
  4    b;
  5  END a;
  6  /

Procedure created.

scott@ORA92> execute a

PL/SQL procedure successfully completed.

scott@ORA92> COLUMN who_am_i FORMAT A13
scott@ORA92> COLUMN who_called_me FORMAT A13
scott@ORA92> COLUMN call_stack	  FORMAT A45
scott@ORA92> SELECT * FROM error_tab
  2  /

WHO_AM_I      WHO_CALLED_ME
------------- -------------
FLOW
----------------------------------------------------------------------------------------------------
CALL_STACK
---------------------------------------------
SCOTT.D       SCOTT.C
PROCEDURE SCOTT.D->PROCEDURE SCOTT.C->PROCEDURE SCOTT.B->PROCEDURE SCOTT.A->ANONYMOUS BLOCK .
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
661B83F8         1  anonymous block
661C8564        13  procedure SCOTT.D
661B3C44         4  procedure SCOTT.C
661AA128         4  procedure SCOTT.B
661B909C         4  procedure SCOTT.A
66299984         1  anonymous block


scott@ORA92>

Report message to a moderator

Re: Procedure execution hierarchy [message #163672 is a reply to message #163671] Fri, 17 March 2006 16:35 Go to previous messageGo to next message
pkirangi
Messages: 74
Registered: August 2005
Member
Thanks Barbara.

Report message to a moderator

Re: Procedure execution hierarchy [message #268191 is a reply to message #163655] Mon, 17 September 2007 22:45 Go to previous messageGo to next message
bikrambhuyan
Messages: 1
Registered: September 2007
Junior Member
Hi,

We have the same requirement to log the stack trace information while logging the error. We are facing issues when trying to implement this in the procudure which are under the package.

But is this valid for the function or procedure within the package?

When we are running the application in debug mode we are finding that the stack trace information only return the SCHEMA.PACKAGE_NAME. This does not return the function name within the package. Also while running the function, it is showing run time error.

Any help on this will be really appreciated.

Thanks,
Bikram.

Report message to a moderator

Re: Procedure execution hierarchy [message #268265 is a reply to message #268191] Tue, 18 September 2007 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What does it gives you when you tried?
Post a test case that we can reproduce.

Basically, a package is an object, a stand-alone procedure is an object, a procedure inside a package is NOT an object.
Oracle only tracks objects.

Regards
Michel

Report message to a moderator

Re: Procedure execution hierarchy [message #410690 is a reply to message #163655] Mon, 29 June 2009 14:47 Go to previous messageGo to next message
shailendrakr
Messages: 2
Registered: June 2009
Junior Member
From the discussion above I understand that format_call_stack is not much help with procedures in a package.
Are there any other options for identifying a procedure in a package? I need this to log exceptions.

Report message to a moderator

Re: Procedure execution hierarchy [message #410693 is a reply to message #410690] Mon, 29 June 2009 15:04 Go to previous message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
Someone wrote a modification to Tom Kyte's code for parsing the procedure name out of the call stack, but it is dependent on the procedure being formatted so the name immediately follows the create and replace on the same line. Tom replied that the line number is much more reliable. You can read the whole discussion here:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1529405950004#167569600346800985

Report message to a moderator

Previous Topic: row records into one column
Next Topic: Need to take different column & row values into a single row (merged)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Nov 24 13:15:00 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.