ORA-6502 while migrating from Oracle 8 to Oracle 9i
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.
Oracle8 Enterprise Edition Release 220.127.116.11.0 - Production
With the Partitioning and Objects options
PL/SQL Release 18.104.22.168.0 - Production
2 Test NUMBER;
4 Test :=' '; --> Blank Space
PL/SQL procedure successfully completed.
Oracle9i Enterprise Edition Release 22.214.171.124.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 126.96.36.199.0 - Production
2 Test NUMBER;
4 Test :=' ';--> Blank Space
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.