java - Calling PL/SQL procedure with SYS_REFCURSOR as IN parameter using JDBC

Question

I am trying to understand how I can call a PL/SQL procedure which takes a SYS_REFCURSOR as IN parameter.

Consider the following PL/SQL procedure:

print_cursor_contents(myCursor SYS_REFCURSOR , row_count OUT NUMBER);

At the time of binding value to the IN parameter which setXXX method do I use ?

To me a java Class with individual cursor record fields , as it members and a Array of instances of this class seems the proper way to represent a plsql CURSOR. I get a SQLException when I do this:

I used the following set method

         callStmt.setObject(1, curRec);

Here is the exception I got for using the above statement:

Exception occured in the database
Exception message: Invalid column type
java.sql.SQLException: Invalid column type
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8921)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8396)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9176)
    at oracle.jdbc.driver.OracleCallableStatement.setObject(OracleCallableStatement.java:5024)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:234)
    at com.rolta.HrManager.printMaxSalAllDept(HrManager.java:1022)
    at com.rolta.HrManager.main(HrManager.java:1116)
Database error code: 17004

Answer 1

To me a java Class with individual cursor record fields , as it members and a Array of instances of this class seems the proper way to represent a plsql CURSOR.

I disagree.

If you have a stored function or procedure that either returns a ref cursor or has a ref cursor as an OUT parameter, the ref cursor comes out of JDBC as a ResultSet. So, if it were possible to call a stored procedure with a SYS_REFCURSOR parameter, I'd suspect that a ResultSet would be what you would need to pass.

In fact, my suspicions are confirmed. If you take a look at Oracle's extension to CallableStatement, OracleCallableStatement, it inherits a setCursor(int, ResultSet) method from its superinterface OraclePreparedStatement. Therefore, you could cast the CallableStatement to OracleCallableStatement, call the setCursor method, and away you go.

Except this approach doesn't actually work.

If you try calling setCursor on an OracleCallableStatement, you will get an exception java.sql.SQLException: Unsupported feature.

You can try callingsetObject with a ResultSet, but you will only get another java.sql.SQLException: Invalid column type exception.

Here's a test class you can run to verify either case. It calls one stored procedure to get a ref cursor (and hence a ResultSet) and then tries to pass it to the other:

import java.sql.*;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.OracleCallableStatement;

public class JavaRefCursorTest {
    public static void main(String[] args) throws Exception {
        Connection conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:XE", "user", "password");

        try (CallableStatement cstmt1 = conn.prepareCall(
                "{ call java_ref_curs_test.get_ref_cursor(?)}")) {
            cstmt1.registerOutParameter(1, OracleTypes.CURSOR);
            cstmt1.execute();

            try (ResultSet rSet = (ResultSet)cstmt1.getObject(1)) {
                try (CallableStatement cstmt2 = conn.prepareCall(
                        "{ call java_ref_curs_test.print_refcursor(?)}")) {

                    // Uncomment the next line to call setCursor:
                    // ((OracleCallableStatement)cstmt2).setCursor(1, rSet); 

                    // Uncomment the next line to call setObject:
                    // cstmt2.setObject(1, rSet);

                    cstmt2.execute();
                }
            }
        }
    }
}

(The two procedures in the java_ref_curs_test take a single SYS_REFCURSOR parameter: get_ref_cursor returns a ref cursor and print_refcursor takes one as a parameter but does nothing with it.)

So, which setXXX method should you use? I would say none of them. What you are asking for is not possible directly.

It may still be possible to call this procedure, but you will have to create the ref cursor in PL/SQL, not in Java, and then pass it to your procedure.

For example, I could use the following PL/SQL block to call the two procedures used in the above example:

DECLARE
   l_curs   SYS_REFCURSOR;
BEGIN
   java_ref_curs_test.get_ref_cursor(l_curs);
   java_ref_curs_test.print_refcursor(l_curs); 
END;

You can fairly easily run this from JDBC: put it in a string and pass it to Statement.executeUpdate().