Calling a PL / SQL Procedure with SYS_REFCURSOR as IN Using JDBC

For me, a java class with separate cursor record fields, as its members and an array of instances of that class seem to be the correct way plsql represent CURSOR.

I disagree.

If you have a stored function or procedure that returns a ref cursor or has a ref cursor as a parameter OUT

, the ref cursor is inferred from JDBC as ResultSet

. So, if it were possible to call the stored procedure with a parameter SYS_REFCURSOR

, I would guess that ResultSet

would be what you would need to pass.

In fact, my suspicions are confirmed. If you look at the Oracle extension to CallableStatement

, OracleCallableStatement

he inherits setCursor(int, ResultSet)

from his superinterface OraclePreparedStatement

. So you can cast the CallableStatement

before OracleCallableStatement

, call the method setCursor

and go.

Also, this approach doesn't work.

If you try to call setCursor

on OracleCallableStatement

, you will get an exception java.sql.SQLException: Unsupported feature

.

You can try calling setObject

with ResultSet

, but you only get a different exception java.sql.SQLException: Invalid column type

.

You can check the test class here to test any case. It calls one stored procedure to get the ref cursor (and therefore ResultSet

) and then tries to pass it to another:

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 java_ref_curs_test

take one parameter SYS_REFCURSOR

: it get_ref_cursor

returns a ref cursor, and it print_refcursor

takes one parameter but does nothing with it.)

So which method setXXX

should you use? I would not say any of them. What you are asking for is not possible directly.

You can still call this procedure, but you would need to create a ref cursor in PL / SQL, not 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 run this from JDBC quite easily: put it on a string and pass it to Statement.executeUpdate()

.