IT37023: SECOND CALL TO ORACLE STORED PROCEDURE HANGS IF SYS_REFCURSOR INCLUDED IN MIDDLE OF PARAMETER LIST

APAR status

• Closed as program error.

Error description

• If an Oracle database stored procedure has been defined to
  return a result set using a SYS_REFCURSOR output parameter, and
  that SYS_REFCURSOR output parameter has been declared in the
  middle of the parameter list in the stored procedure signature
  rather than at the end, a second call to the procedure may hang
  (or time out).
  

Local fix

• Redefine the Oracle database stored procedure to move the
  SYS_REFCURSOR to the end of the parameter list.
  Alternatively, set the environment variable
  MQSI_EMPTY_DB_CACHE=1 to force a re-prepare of the SQL statement
  for each database call.
  

Problem summary

• ****************************************************************
  USERS AFFECTED:
  All users of IBM Integration Bus V10 and IBM App Connect
  Enterprise V11 and V12 using Oracle ODBC connections.
  
  
  Platforms affected:
  MultiPlatform
  
  ****************************************************************
  PROBLEM DESCRIPTION:
  If an Oracle database stored procedure has been defined to
  return a result set using a SYS_REFCURSOR output parameter, and
  that SYS_REFCURSOR output parameter has been declared in the
  middle of the parameter list in the stored procedure signature
  rather than at the end, a second call to the procedure may hang
  (or time out).
  

Problem conclusion

• The product has been updated with new ODBC Oracle drivers that
  resolve the problem.
  
  ---------------------------------------------------------------
  The fix is targeted for delivery in the following PTFs:
  
  Version    Maintenance Level
  v10.0      10.0.0.25
  v11.0      11.0.0.15
  v12.0      12.0.3.0
  
  The latest available maintenance can be obtained from:
  http://www-01.ibm.com/support/docview.wss?rs=849&uid=swg27006041
  
  If the maintenance level is not yet available,information on
  its planned availability can be found on:
  http://www-1.ibm.com/support/docview.wss?rs=849&uid=swg27006308
  ---------------------------------------------------------------
  

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

  INTEGRATION BUS

• Fixed component ID

  5724J0540

Applicable component levels