Java calling a stored proc with ref cursor return type

callable-statementjavajdbcplsqlstored-procedures

I've been encountering an issue where I constantly get a java.sql.SQLException: Bigger type length than Maximum error when trying to return a ref cursor from my stored procedure.

I have a very simple stored procedure which has just one output variable as a ref cursor, the ref_cursor is defined to be type ref cursor in the Oracle package named WS_SEARCHTEST.

CREATE OR REPLACE PACKAGE DB.WS_SEARCHTEST
IS
TYPE ref_cursor IS REF CURSOR;

My procedure:

PROCEDURE searchTest(query_result OUT ref_cursor)
Is
Begin
   open query_result for select pay_id, member_id, group_no from member_table
where carrier_no = '7' AND group_no = '200607';
End;

I know my connection works because I've used it to run simple queries and have checked with the db. In my java code I do the following:

callStmt = connection.prepareCall("{call WS_SEARCHTEST.searchTest(?)}");
callStmt.registerOutParameter(1, OracleTypes.CURSOR);
callStmt.execute();
resultSet = (ResultSet) callStmt.getObject(1);

I have also tried:

 resultSet = callStmt.executeUpdate();    

AND

 resultSet = callStmt.executeQuery();

However I will always get java.sql.SQLException: Bigger type length than Maximum when I try to access the resultSet.

Thanks

Best Solution

if you want to write a java code which will call PostgreSQL Stored procedure where 
there is an INOUT refcursor.
PostgreSQL SP:

 CREATE OR REPLACE PROCEDURE  Read_DataDetails(INOUT  my_cursor REFCURSOR = 'rs_resultone')
 LANGUAGE plpgsql
 AS $$                                                    
 BEGIN
 OPEN my_cursor FOR Select * from MyData;      
 END;
 $$;

 corresponding java code will be:

connection = getConnection(your connection parameter);
if (connection != null) {
connection.setAutoCommit(false); // This line must be written just after the 
//connection open otherwise it will not work since cursor will be closed immediately. 
String readMetaData = "CALL Read_DataDetails(?)";
callableStatement = connection.prepareCall(readMetaData);
callableStatement.registerOutParameter(1, Types.REF_CURSOR);
callableStatement.execute();
rs = (ResultSet) callableStatement.getObject(1);
if (rs != null) {
    while (rs.next()) {
                 <Your Logic>
                }//end while
            }//end if

}//end if