Error occurs when binding Oracle REF CURSOR of a stored procedure as output parameter using the Connect for JDBC Oracle driver.

Jul 2, 2015
Knowledge

Title

URL Name

Invalid-parameter-binding-s-error-occurs-when-binding-SYS-REFCURSOR-of-a-stored-procedure-as-out-param

Article Number

000176018

Environment

Product: Connect for JDBC Oracle driver. Version: 5.1 OS: Java Database: Oracle Application: All supported applications

Question/Problem Description

Error occurs when binding Oracle REF CURSOR of a stored procedure as output parameter using the Connect for JDBC Oracle driver.

Steps to Reproduce

Clarifying Information

Error Message

[DataDirect][Oracle JDBC Driver]Invalid parameter binding(s).

Defect Number

Enhancement Number

Cause

JDBC does not support a cursor variable data type and the driver converts the REF CURSOR parameter into a result set instead of returning as output parameter.

Resolution

In your application, omit any parameter markers for the REF CURSOR as shown in the example below and in following link and do not declare an output parameter for the REF CURSOR.

See the Connect for JDBC Userguide at : DataDirect Connect Drivers : Oracle Driver : Returning and Inserting/Updating XML Data : REF CURSOR Data Type

Example :

Stored Procedure definition :

CREATE PACKAGE foo_pkg AS
TYPE EmpCurTyp IS REF CURSOR RETURN fooTbl%ROWTYPE;
PROCEDURE selectEmployeeManager(empId IN INT, empCursor OUT EmpCurTyp,
mgrCursor out EmpCurTyp);
FUNCTION selectEmployee2 (empId IN INT) return EmpCurTyp;
END foo_pkg;

Sample Code to return Ref Cursor :

// Call a function that accepts an input parameter and returns a
// REF CURSOR as the return value.
// Omit the placeholder for the refcursor return value parameter.
// The REF CURSOR is returned as a result set.
sql = "{call foo_pkg.selectEmployee2(?)}";
callStmt = con.prepareCall(sql);
callStmt.setInt(1, 2);
moreResults = callStmt.execute();
while (true) {
if (moreResults) {

// Get the result set that represents the REF CURSOR
resultSet = callStmt.getResultSet();
displayResults(resultSet);

resultSet.close();
resultSet = null;

System.out.println();
}
else {

updateCnt = callStmt.getUpdateCount();
if (updateCnt == -1) {
break;
}
System.out.println("Update Count: " + updateCnt);
}
moreResults = callStmt.getMoreResults();
}

Workaround

Notes

Example in TestforJDBC:

Create this package & procedure on your Oracle database. If you don't already have a table named EMP, create that as well and insert a row of test data.

CREATE OR REPLACE Package pkg_RCsr_Test
IS
TYPE curJDBC IS REF CURSOR;
PROCEDURE GetEmpCursor(rspCursor IN OUT curJDBC);
END pkg_RCsr_Test;

CREATE OR REPLACE Package Body pkg_RCsr_Test
IS
PROCEDURE GetEmpCursor(rspCursor IN OUT curJDBC)
IS
BEGIN
OPEN rspCursor FOR
SELECT * FROM EMP;
END GetEmpCursor;
END pkg_RCsr_Test;

Open Test for JDBC and connect to that Oracle database.
Create the callable statement {call pkg_RCsr_Test.GetEmpCursor} and execute it.
Use Show All Results to return the data.

You will see the EMP table data returned.
This is the value of the IN OUT parameter rspCursor. While you did not declare an IN OUT parameter when calling the procedure, the driver automatically returns the value of that parameter as a result set

Keyword Phrase

Last Modified Date

8/30/2016 9:08 AM

Error occurs when binding Oracle REF CURSOR of a stored procedure as output parameter using the Connect for JDBC Oracle driver.

Files(0)

Was this article helpful?

Was this article helpful?

Error occurs when binding Oracle REF CURSOR of a stored procedure as output parameter using the Connect for JDBC Oracle driver.

Information

Related Articles