| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Obtainning call stack list in PL/SQL
A copy of this was sent to "MARC, MAZEROLLE" <mazerolle.marc_at_bell.ca>
(if that email address didn't require changing)
On Fri, 22 Oct 1999 09:10:30 -0400, you wrote:
>Hi all,
>
>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 ?
>
This stuff is all already in the alert log -- the C programs (sqlplus, toad, ora_snp, etc) get this error stack but the top level plsql program cannot get it (as the call stack unwinds, we lose this information in plsql -- once you catch the exception -- its gone. only if the exception propagates all the way out of the database can the calling app get it again -- you would have to put the error handler at the lowest level in plsql).
Look in your alert log, you'll see entries like this:
Tue Oct 19 12:13:17 1999
Errors in file /export/home/oracle8i/admin/ora8i/bdump/ora8i_snp2_7150.trc:
ORA-12012: error on auto execute of job 111742
ORA-29532: Java call terminated by uncaught Java exception:
java.lang.NullPointerException
ORA-06512: at "UTILS.SIMPLE_TCP_CLIENT", line 24 ORA-06512: at "UTILS.SOCKETTYPE", line 37 ORA-06512: at "UTILS.UTL_SMTP", line 83 ORA-06512: at "UTILS.UTL_SMTP", line 76 ORA-06512: at "UTILS.MAILPKG", line 39 ORA-06512: at line 1
The entire call stack is put in there by the ora_snp process for you.
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Oct 22 1999 - 14:36:30 CDT
 |
 |