ORA-6502 while migrating from Oracle 8 to Oracle 9i

[ksridhar]

Dear All,
We are migrating from Oracle 8 to Oracle 9i and we are facing the ORA-6502 error. Please find an example to illustrate the same.

ORA-06502: PL/SQL: numeric or value error

You receive the error ORA-06502. On each release of Oracle, the PL/SQL engine is becoming more strict. Generally, some “implicit” data type conversions which were still accepted in Oracle 8 (mainly 8.0, 8.1 was also already a lot stricter) are not accepted anymore in Oracle 9.
The problem is that most of the errors that will happen around this are mostly programming errors, the problem is that Oracle accepted these “errors” in the past, which it shouldn’t have in the first place.

For instance, following piece of code functions well on Oracle version 8.0.6, but does not function anymore on Oracle 9.2.0. This piece of code does not function anymore in 8.1.7 either.
But : this piece of code should have never been accepted, as it is not logical to assign a character to a number field.

Examples

Connected to:
Oracle8 Enterprise Edition Release 8.0.6.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.6.0.0 - Production

SQL> DECLARE
2 Test NUMBER;
3 BEGIN
4 Test :=' '; --> Blank Space
5 END;
6 /

PL/SQL procedure successfully completed.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> DECLARE
2 Test NUMBER;
3 BEGIN
4 Test :=' ';--> Blank Space
5 END;
6 /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4

Is there any way or tool available to cast this error. As this is purely data driven.

Thanks and Regards
Sridhar

[ksridhar]

I was able to solve this problem, Please find below
As this is Bug in Oracle:571545

SQL> alter session set events = '10943 trace name context level 4';

Session altered.

SQL> DECLARE
2 Test NUMBER;
3 TEST1 VARCHAR2(10) DEFAULT ' ';
4 BEGIN
5 Test :=TO_NUMBER(TEST1);
6 END;
7 /

PL/SQL procedure successfully completed.

Thanks for all the guys who were trying to Help me.