How to Trap ORA-6502 Error Stack Information and Insert into a Table using PL/SQL? (Doc ID 605225.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version: 10.2.0.4 to 11.1.0.6
Information in this document applies to any platform.
***Checked for relevance on 19-Feb-2010***

Goal

The DBMS_UTILITY package provides various utility subprograms and the one illustrated in this Note enables us to store information about an error. The following functions from the DBMS_UTILITY package that will be used in this Note are:

FORMAT_CALL_STACK  - Formats the current call stack

29 ----- PL/SQL Call Stack -----
object      line      object
handle      number
name
385b5af28    23       anonymous block

 
FORMAT_ERROR_STACK - Formats the current error stack 

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

FORMAT_ERROR_BACKTRACE - Formats the backtrace from the point of the current error to the exception handler where the error has been caught.  (Look in the USER_SOURCE table for the failing line.)

ORA-06512: at line 12


The following code demonstrates how this information can be inserted into an error log table.

Note: ORA-6502 can be raised in the declaration part of the PL/SQL program by faulty initialization expressions.  Handlers in the current block cannot catch the raised exception because an exception raised in a declaration propagates immediately to the enclosing block.  The sample code has nested the failing code so that the error can be trapped.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms