Obtaining call stack list in PL/SQL

Recently, one of our developpers asked me the following question :

Question :

In SQL*Plus (in TOAD also), when an un-handled exception is raised at execution time, SQL*Plus will give an error message like this on :

  1 begin
  2 x.x1;
  3* end;
SQL> /
begin
*
ERROR at line 1:

ORA-20000: Erreur
ORA-06512: at "NTC_ADMIN2.X", line 10
ORA-06512: at "NTC_ADMIN2.X", line 4
ORA-06512: at line 2

SQL> We use DBMS_JOBS to schedule batch jobs and would like to catch un-handle exceptions error stack and call stack (like the one given by SQL*Plus) of jobs when they crash into a log.

Can a PL-SQL block obtain the error and call stack list as displayed by SQL*Plus ?

• End question

Now, of course, i did not relay this request directly to this newsgroup without some research...

I found the DBMS_UTILITY.FORMAT_CALL_STACK and DBMS_UTILITY.FORMAT_ERROR_STACK procedure and tried the following :

Test 1 :

create or replace package x as

    procedure x1;
    procedure x2;
end;
/

create or replace package body x as
procedure x1 is
begin

    x.x2;
end;
procedure x2 is
vx varchar2(4000);
vz varchar2(4000);
begin

    raise_application_error(-20000,'Erreur',true); exception when others then

    vx := dbms_utility.format_Error_stack;     vz := dbms_utility.format_call_stack;     dbms_output.put_line('ERROR');
    dbms_output.put_line('----Error stack----');     if vx is null then

        dbms_output.put_line('vx is empty');     else

        dbms_output.put_line('vx length is '||length(vx));     end if;
    dbms_output.put_line(vx);
    dbms_output.put_line('----Call stack----');     if vz is null then

        dbms_output.put_line('vz is empty');     else

        dbms_output.put_line('vz length is '||length(vz));     end if;
    dbms_output.put_line(vz);
    raise;
end;
end;
/

set serveroutput on size 20000

begin

    x.x1;
end;
/

ERROR
----Error stack----
vx length is 18
ORA-20000: Erreur

----Call stack----
vz length is 210
----- PL/SQL Call Stack -----
  object line object
  handle number name

c4a0d2b8        13  package body NTC_ADMIN2.X
c4a0d2b8         4  package body NTC_ADMIN2.X
c5007168         2  anonymous block

begin
*
ERROR at line 1:

ORA-20000: Erreur
ORA-06512: at "NTC_ADMIN2.X", line 29
ORA-06512: at "NTC_ADMIN2.X", line 4
ORA-06512: at line 2

SQL> Looks pretty good and i returned this to the developper....

He came back and said that it would require every packages, procedure and function to be visited and modified to work... Not good enough. How come SQL*Plus (and TOAD) can do this ?

Test 2:

• Move the reading of the call stack down to the anonymous block.

create or replace package x as

    procedure x1;
    procedure x2;
end;
/

create or replace package body x as
procedure x1 is
begin

    x.x2;
end;
procedure x2 is
vx varchar2(4000);
vz varchar2(4000);
begin

    raise_application_error(-20000,'Erreur',true); end;
end;
/

set serveroutput on size 20000

declare
vx varchar2(4000);
vz varchar2(4000);
begin

    x.x1;
exception when others then

    vx := dbms_utility.format_Error_stack;     vz := dbms_utility.format_call_stack;     dbms_output.put_line('ERROR');
    dbms_output.put_line('----Error stack----');     if vx is null then

        dbms_output.put_line('vx is empty');     else

        dbms_output.put_line('vx length is '||length(vx));     end if;
    dbms_output.put_line(vx);
    dbms_output.put_line('----Call stack----');     if vz is null then

        dbms_output.put_line('vz is empty');     else

        dbms_output.put_line('vz length is '||length(vz));     end if;
    dbms_output.put_line(vz);
end;
/

ERROR
----Error stack----
vx length is 18
ORA-20000: Erreur

----Call stack----
vz length is 118
----- PL/SQL Call Stack -----
  object line object
  handle number name
c462bf60 8 anonymous block

Now i don't have the complete stack....

I tried all kinds of other ways to no avail. I also look in events but that did not work to good. So i turn to the gurus.... you.

This is not easy and should be possible.

Please don't answer to tell me to spool the SQL*Plus output to a file and read it.... this is not an option and is the easy way out.... i don't have access to SQL*Plus (or TOAD) from a DBMS_JOBS scheduled job. All i can do is modify the anonymous block passed to DBMS_JOBS.

Regards,

Marc Mazerolle
InforMaze Technologies. Received on Mon Oct 25 1999 - 14:34:56 CEST