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:
Copyright and trademark information
IBM, the IBM logo and ibm.com are trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at "Copyright and trademark information" at www.ibm.com/legal/copytrade.shtml.