ORA-6502 while migrating from Oracle 8 to Oracle 9i
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
