JR52083: ORACLE CONNECTOR JOB FAILING WITH ERROR MESSAGE: ORA-00932

IBM InfoSphere Information Server, Version 11.3.1.2 (Fix Pack 2)

APAR status

• Closed as program error.

Error description

• Job is failing with Error code: 932, Error message: ORA-00932:
  inconsistent datatypes: expected NUMBER got CLOB.
  

Local fix

• In the column length in the Oracle Connector if it is set to
  less than 43, you can increase to 45 or more and the error
  message will stop showing up.
  

Problem summary

• Oracle Connector job is fails with
  Error code: 932, Error message: ORA-00932: inconsistent
  datatypes: expected NUMBER got CLOB.
  

Problem conclusion

• In v9.1, with Big Buffer performance enhancement, the Oracle
  Connector started using the Packed Decimal Numeric type
  (SQLT_PDN) for the OCI external datatype of decimal columns
  instead of character string (SQLT_CHR).  A failing job has
  special columns. In this context "special columns" means columns
  that use the extract / xmltype / text / getclobval functions
  something like this:
  
  extract( xmltype (FORMDATA),
  '//DirectValuationEntry_v2/Summary/ReplacementCost/text()'
  ).getclobval() as TOTAL_ESTIMATED_REPL_COST_AM
  
  This fix is to change the code to honor a new environment
  variable that will specify use of the SQLT_CHR external
  datatype.  The environment variable is
  CC_ORA_BIND_DECIMAL_AS_CHAR.  Customer sets this to TRUE or
  FALSE in the job or project.
  

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

  WIS DATASTAGE

• Fixed component ID

  5724Q36DS

Applicable component levels

• R910 PSY

     UP

• R912 PSY

     UP

• RB30 PSY

     UP

• RB31 PSY

     UP