Calling Oracle stored procedure with both parameter bindings and reference cursors fails

Technote (troubleshooting)


Problem(Abstract)

Executing an Oracle stored procedure that has both parameter bindings and Oracle reference cursors fails on the IBM WebSphere DataPower SOA Appliance.


Sample snippet of such an sql-execute call:

<dp:sql-execute source="'SQLDataSourceObj'" statement="'{CALL STORED_PROC_NAME(?,?,?)}'">
<arguments>
<argument type="SQL_VARCHAR" mode="INPUT">
02100002
</argument>
<argument type="SQL_VARCHAR" MODE="OUTPUT"/>
<argument mode="OUTPUT"/> <!-- Oracle ref cursor output argument -->
</arguments>
</dp:sql-execute>



Symptom

An error similar to the following will be logged:

sql-source (SQLDataSourceObj): Database reported query error: ORA-06550: line 1, column 8: PLS-00306: wrong number or types of arguments in call to 'STORED_PROC_NAME' ORA-06550: line 1, column 8: PL/SQL: Statement ignored

sql-source (SQLDataSourceObj): Database reported query error: ORA-06550: line 1, column 46: PLS-00302: component 'STORED_PROC_NAME' must be declared ORA-06550: line 1, column 8: PL/SQL: Statement ignored


Cause

Executing a stored procedure that has both parameter bindings and Oracle reference cursors is currently not supported.

Resolving the problem

The current support for executing reference cursors is as documented in the following developerWorks article and technote:

Using Oracle cursors in WebSphere DataPower SOA Appliances

Invalid parameter binding(s) error when calling a stored procedure with a reference cursor output parameter

Rate this page:

(0 users)Average rating

Document information


More support for:

WebSphere DataPower SOA Appliances
General

Software version:

4.0.2, 5.0.0, 6.0.0, 6.0.1

Operating system(s):

Firmware

Reference #:

1596616

Modified date:

2013-02-05

Translate my page

Machine Translation

Content navigation