JR39863: ORACLE CONNECTOR REWRITES UPDATE STATEMENT WITH RETURNS CLAUSE THAT DUPLICATES EXISTING ORDINAL PARAMETER

IBM InfoSphere Information Server, Version 8.5 Fix Pack 2

APAR status

• Closed as program error.

Error description

• When Oracle Connector rewrites an UPDATE statement, adding a
  RETURNS clause to it, it is incorrectly using the count of
  parameters for the ordinal of the RETURNS clause. This causes
  UPDATE statements that use ordinal parameter notation to have
  the RETURNS clause bound to a parameter that is already in use.
  
  For example,
  UPDATE test SET B=:2 WHERE A=:1
  
  is rewritten as
  
  UPDATE TEST SET B=:2 WHERE A=:1 RETURNING COUNT(ROWID) INTO :2
  
  it should instead be
  
  UPDATE TEST SET B=:2 WHERE A=:1 RETURNING COUNT(ROWID) INTO :3
  

Local fix

• Use name parameters instead of ordinal parameters:
  
  UPDATE TEST SET B=:B WHERE A=:A
  

Problem summary

• Oracle Connector adds a RETURNS clause that reuses an existing
  ordinal parameter
  

Problem conclusion

• Oracle Connector was incorrectly using the count (instead of
  count+1) of a 1-based index as the next ordinal to use.
  

Temporary fix

• Do not use ordinal parameters.
  

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

• R850 PSY

     UP