JR35730: USE OF NAMED BIND PARAMETERS IN PL/SQL BLOCK CAUSES ORA-01036

Download Fix Pack 2 for InfoSphere Information Server Version 8.1

APAR status

• Closed as program error.

Error description

• When using PL/SQL anonymous block with named bind parameters
  (:param_name) in Oracle Connector stage, the job fails with
  Oracle native error ORA-01036: illegal variable name/number.
  This happens even if the names of all bind parameters match
  names of columns on the input link of the stage.
  The job works however if numeric bind parameters (:n) are used
  with the order of parameter numbers matching the order of input
  columns to which they correspond (bind).
  

Local fix

Problem summary

• ****************************************************************
  USERS AFFECTED:
  Information Server users who are utilizing Oracle Connector
  stage
  ****************************************************************
  PROBLEM DESCRIPTION:
  When the connector is used in PL/SQL write mode and the provided
  PL/SQL anonymous block contains named bind parameters, in some
  cases ORA-01036 error is returned even if all bind parameters
  have matching columns on the input link of the stage. The
  problem typically occurs when one bind parameter has a name that
  is a substring of another parameter's name.
  ****************************************************************
  RECOMMENDATION:
  Install patch JR35730
  ****************************************************************
  

Problem conclusion

• The connector code was fixed to properly parse parameter names
  in the specified PL/SQL block and to match them against the
  columns defined on the input link of the connector stage.
  

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

• R810 PSN

     UP