Dbms_Utility.format_error_stack only returns 1st error !?

From: Karl E. Jørgensen <kjorg_at_msn.com>
Date: 1997/12/13
Message-ID: <#DhZnA2B9GA.237@upnetnews04>#1/1

This is a multi-part message in MIME format.

------=_NextPart_000_00A7_01BD075F.F85BDF60 Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Initially I'd have thought that the DBMS_UTILITY.FORMAT_ERROR_STACK = function returned "the error stack" !

So, If I have a procedure (which calls loads of other procedures) and = want the CALLING procedure to report any errors, I expected to be able = to just "catch" it in a "when others" exception, then use = format_error_stack to report the error, and continue processing.

In short, this is driving me nuts (some may say that's too late anyway), = but am I missing something obvious ? Or am I just (heaven forbid !) = WRONG ? I'd appreciate any suggestions you may have

Karl

Sample SQL*Plus output SQL> create or replace Procedure proc_c Is 2 Begin 3 raise no_data_found; 4 End proc_c; 5 / Procedure created. SQL> create or replace Procedure proc_b Is 2 Begin 3 proc_c; 4 End proc_b; 5 / Procedure created. SQL> create or replace Procedure proc_a Is 2 Begin 3 proc_b; 4 End proc_a; 5 / Procedure created. SQL> Begin 2 proc_a; 3 End; 4 / Begin * ERROR at line 1: ORA-01403: no data found ORA-06512: at "SCOTT.PROC_C", line 3 ORA-06512: at "SCOTT.PROC_B", line 3 ORA-06512: at "SCOTT.PROC_A", line 3 ORA-06512: at line 2

### Good - this was what I expected.
### Now try to catch it in an exception
=20
SQL> Begin
  2 proc_a;
  3 Exception When others Then
  4 DBMS_Output.Put_Line(DBMS_Utility.format_error_stack);   5 End;
  6 /
ORA-01403: no data found
=20
=20
PL/SQL procedure successfully completed.

###' yep - I caught it. BUT WHERE IS THE REST OF THE ERROR STACK !?=20 SQL> ------=_NextPart_000_00A7_01BD075F.F85BDF60 Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 HTML//EN">
<HTML>
<HEAD>

<META content=3Dtext/html;charset=3Diso-8859-1 =
http-equiv=3DContent-Type>
<META content=3D'"MSHTML 4.72.2106.6"' name=3DGENERATOR>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT color=3D#000000 size=3D2>Initially I'd have thought that the=20
DBMS_UTILITY.FORMAT_ERROR_STACK function returned "the error = stack"=20
!</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
<DIV><FONT size=3D2>So, If I have a procedure (which calls loads of =
other=20
procedures) and want the CALLING procedure to report any errors, I = expected to=20
be able to just "catch" it in a "when others" = exception,=20
then use format_error_stack to report the error, and continue=20 processing.</FONT></DIV>
<DIV><FONT size=3D2></FONT> </DIV>
<DIV><FONT size=3D2>In short, this is driving me nuts (some may say =
that's too=20
late anyway), but am I missing something obvious ? Or am I just (heaven = forbid=20
!) WRONG ?</FONT></DIV>
<DIV><FONT size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000 size=3D2>I'd appreciate any suggestions you =
may=20
have</FONT></DIV>
<DIV> </DIV>
<DIV><FONT size=3D2>Karl</FONT></DIV>
<DIV> </DIV>
<DIV><FONT color=3D#000000 size=3D2>--- Sample SQL*Plus =
output</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2>SQL> create or replace Procedure =
proc_c=20
Is<BR>  2  Begin<BR>  3     raise=20 no_data_found;<BR>  4  End proc_c;<BR>  5  = /<BR>Procedure=20
created.<BR>SQL> create or replace Procedure proc_b Is<BR>  = 2 =20
Begin<BR>  3     proc_c;<BR>  4  End=20 proc_b;<BR>  5  /<BR>Procedure created.<BR>SQL> create or = replace=20
Procedure proc_a Is<BR>  2  Begin<BR>  = 3    =20
proc_b;<BR>  4  End proc_a;<BR>  5  /<BR>Procedure=20 created.<BR>SQL> Begin<BR>  2     = proc_a;<BR> =20
3  End;<BR>  4  /<BR>Begin<BR> *<BR>ERROR at line=20 1:<BR>ORA-01403: no data found<BR>ORA-06512: at = "SCOTT.PROC_C", line=20
3<BR>ORA-06512: at "SCOTT.PROC_B", line 3<BR>ORA-06512: at=20 "SCOTT.PROC_A", line 3<BR>ORA-06512: at line = 2<BR></FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2>### Good - this was what I=20
expected.</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2>### Now try to catch it in an=20
exception<BR> <BR>SQL> Begin<BR>  2     =

proc_a;<BR>  3  Exception When others Then<BR> =20 4    =20
DBMS_Output.Put_Line(DBMS_Utility.format_error_stack);<BR>  5  =

End;<BR>  6  /<BR>ORA-01403: no data=20 found<BR> <BR> <BR>PL/SQL procedure successfully=20 completed.<BR></FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2>###' yep - I caught it. BUT WHERE IS =
THE REST OF=20
THE ERROR STACK !? <BR>SQL></FONT></DIV></BODY></HTML>

------=_NextPart_000_00A7_01BD075F.F85BDF60-- Received on Sat Dec 13 1997 - 00:00:00 CST