SQL & PL/SQL

Hello,

I have a question :
When an error such as ORA-01438 : value larger than specified precision allows…..

I am able to use
invalid_reference_number EXCEPTION;
PRAGMA EXCEPTION_INIT (invalid_reference_number, -1438);

In order to trap the error and display a message to the user.
I am also able to do this with several other errors.

HOWEVER with the following error:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error

One thing that is different from other errors is that it has “PL/SQL” in the error line.

On page 134/135 of the O’reilly text Oracle PL/SQL Programming – this error is referred to as one of the “named system exceptions”. In other words it is given a name VALUE_ERROR

As I understand it I should be able to put this in my EXCEPTION section:

EXCEPTION
WHEN VALUE_ERROR THEN
HTP.print (‘There is a problem with the reference number’);
END;

However it is not currently functioning
(Note: VALUE_ERROR is also given another name and I have tried that also)

I have got it to work with another named system exception

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
HTP.print (‘This works’);
END;

I have also tried a wide variety of other things such as
I assign SQLcode to error_code and I do that following

IF error_code =-6502 THEN
HTP.print (‘It works with other errors’);
END IF;

I am searching for a solution where I print something to the browser when the ORA-06502 occurs

I have been successful with all my other errors.

Any advice or suggestions would be appreciated

Doug