6 Replies Latest reply: May 12, 2013 1:37 PM by meet_sanc RSS

    ORA-01722: invalid number - Error during implicit conversion (10g vls 11g)

    meet_sanc
      I am facing oracle error for few SELECT queries in 11g which were working fine in 10g environment.
      Oracle Version:
      10g - 10.2.0.5.0
      11g - 11.2.0.3.0

      We are storing numeric values under CHAR or VARCHAR2 column in few tables. This is known design issue & it can't be changed for now.
      I know this will work fine until all the data is numeric in those columns for respective table. Because by some reason, if any row got character value for that column then implicit string to number conversion will fail and this error can occur. But I have verified that there is no data issue.

      But I can see difference in the execution plan! But not able to recognize the reason issue.
      Any other pointers on why the queries are failing in 11g would be of great help..

      Example SQL:
      Note that table structure and data is same in both the environments.
      View Definition - 
      CREATE OR REPLACE FORCE VIEW CIMSOS.LC_LOCATIONS
      (   COUNTRY_ID,    PRODUCTID,
         LANGUAGE_ID,   LOCATION_NAME,
         LOCATION_SHORT_NAME,   TAG_ID,   LOCATION_DEFINITION,
         COMPOSITEKEY,   GLOBAL_LOCATION_KEY,
         CPRODUCTKEYID,   CMODVERSION)
      AS
         SELECT 
                CCOUNTRY_ID AS COUNTRY_ID,
                CPRODUCTID AS PRODUCTID,
                CLANGUAGE_CODE AS LANGUAGE_ID,
                CLOCATION_NAME AS LOCATION_NAME,
                CLOCATION_SHORT_NAME AS LOCATION_SHORT_NAME,
                CTAG_ID AS TAG_ID,
                CLOCATION_DEFINITION AS LOCATION_DEFINITION,
                CCOMPOSITEKEY AS COMPOSITEKEY,
                CGLOBAL_LOCATION_KEY0 AS GLOBAL_LOCATION_KEY,
                cproductkeyid,
                cmodversion
           FROM mct_35193, goldencopy
          WHERE     cproductkeyid = productkey
                AND cmodversion = version
                AND catalogid = 35193;
      Facing issue for SQL query based on above view:
      ** In 10g Env
      SELECT * FROM LC_LOCATIONS WHERE productid = 75;
      -- It executes successfully and provide required results.
      
      Explain Plan:
      
      SELECT STATEMENT  ALL_ROWSCost: 12  Bytes: 416  Cardinality: 4                 
           4 NESTED LOOPS  Cost: 12  Bytes: 416  Cardinality: 4            
                1 INDEX RANGE SCAN INDEX CIMSOS.GOLDENCOPY_INDX1 Cost: 10  Bytes: 30,225  Cardinality: 2,015       
                3 TABLE ACCESS BY INDEX ROWID TABLE CIMSOS.MCT_35193 Cost: 1  Bytes: 89  Cardinality: 1       
                     2 INDEX UNIQUE SCAN INDEX (UNIQUE) CIMSOS.XPK_MCT_34342 Cost: 0  Cardinality: 1  
      ** In 11g Env
      SELECT * FROM LC_LOCATIONS WHERE productid = 75;
      ORA-01722: invalid number
      
      Explain Plan:
      
      SELECT STATEMENT  ALL_ROWSCost: 40  Bytes: 8,692  Cardinality: 82            
           3 NESTED LOOPS  Cost: 40  Bytes: 8,692  Cardinality: 82       
                1 TABLE ACCESS FULL TABLE CIMSOS.MCT_35193 Cost: 22  Bytes: 819  Cardinality: 9  
                2 INDEX RANGE SCAN INDEX CIMSOS.GOLDENCOPY_INDX1 Cost: 2  Bytes: 135  Cardinality: 9  
      
      -- Executes when value is passed as character.
      SELECT * FROM LC_LOCATIONS WHERE productid = '75';