JR37338: ERROR "ORA-01722: INVALID NUMBER" LOGGED IN DB.LOG FOR QUERY ON TCTG_CAA_CATALOG_ATTRIBUTES TABLE WHILE OPENING SPECS

APAR status

• Closed as program error.

Error description

• When opening or saving a spec, an error
  "ORA-01722: invalid number"
  might get logged in db.log.
  
  The query and error stack looks like:
  
  2010-06-24 13:47:45,775 [jsp_266: /ccd_content/spec_tree.jsp]
  ERROR com.ibm.ccd.common.db.Query - CWPCM0040E:The query failed
  : [ Dynamic Query [
  
   select distinct ctg_name from tctg_ctg_catalog,
  tctg_caa_catalog_attributes
    where ctg_id = caa_catalog_id
      and caa_name = 'SPEC_ID'
      and CAA_VALUE = 402
      and caa_next_version_id = 999999999] ],
  
  java.sql.SQLSyntaxErrorException: ORA-01722: invalid number at
  oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMappi
  ng.java:91)
  
  ...
  
  at com.ibm.ccd.element.view.PrimaryKeyNodeView._getSpecDetails
  at com.ibm.ccd.element.view.NodeView.getSpecDetails
  
  This problem is related to Oracles automatic implicit datatype
  conversion mechanism and has been found only so far on Oracle
  11.1.0.6.0
  

Local fix

• upgrade to latest Oracle release
  

Problem summary

• ****************************************************************
  USERS AFFECTED:
  All  Master Data Management Server for Product Information
  Management implementation built upon WebSphere Application
  Server using Oracle as the database are affected by this issue
  ****************************************************************
  PROBLEM DESCRIPTION:
  ERROR "ORA-01722: INVALID NUMBER" LOGGED IN DB.LOG FOR QUERY
  ON TCTG_CAA_CATALOG_ATTRIBUTES TABLE WHILE OPENING SPECS
  ****************************************************************
  RECOMMENDATION:
  IBM recommends that customers facing the issue reported by this
  APAR apply  Master Data Management Server for Product
  Information Management version 9.0.0-FP004  to gain resolution
  to this problem.
  ****************************************************************
  

Problem conclusion

• Automatic conversion from Varchar to integer not happening
  correctly in Oracle 11.2.0.6. Changing the query to not use
  conversion.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  MDM SERVER FOR

• Fixed component ID

  5724V5100

Applicable component levels

• R900 PSN

     UP