ORA-6502 while migrating from Oracle 8 to Oracle 9i
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

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

  1. #1
    Join Date
    Dec 2002
    Location
    India
    Posts
    34

    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

  2. #2
    Join Date
    Dec 2002
    Location
    India
    Posts
    34
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width