Map SYS_REFCURSOR parameter from stored procedure

Question

I have stored procedure in Oracle DB. It's output parameters are Number ("RETURN_VALUE") and three parameters of SYS_REFCURSOR type ("ONE", "TWO"...). I know how to call this procedure and to get numeric parameter:

SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate)
        .withProcedureName("NAME")
        .withReturnValue();
Map<String, Object> response = call.execute(new MapSqlParameterSource(...));
Integer responseCode = (Integer) response.get("RETURN_VALUE");

But how to map SYS_REFCURSOR parameter to some List of DesiredClassType?

List<DesiredClassType> list = (List<DesiredClassType>) response.get("ONE");

Answer 1

I found a solution for this problem by using a CallableStatement instead of SimpleJdbcCall.

@Component
public class DaoExample {

    private static final String PROCEDURE_CALL_SQL = "{call SCHEME.PROC_NAME(?,?,?,?,?,?,?,?,?)}";

    private DataSource dataSource;

    DaoExample(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public void callProcedure() {
 
        try (Connection con = dataSource.getConnection();
             CallableStatement stmt = con.prepareCall(PROCEDURE_CALL_SQL)) {
            con.setAutoCommit(false);

            // set input parameters
            stmt.setInt("input_parameter_name1", 1);
            stmt.setString("input_parameter_name2", "2");
            stmt.setNull("input_parameter_name3", OracleTypes.VARCHAR);
            stmt.setDate("input_parameter_name4", Date.valueOf("2021-04-23"));
        
            // set output parameters
            stmt.registerOutParameter("output_parameter_name1", OracleTypes.NUMERIC);
            stmt.registerOutParameter("output_parameter_name2", OracleTypes.CURSOR);
            stmt.registerOutParameter("output_parameter_name3", OracleTypes.CURSOR);
            stmt.registerOutParameter("output_parameter_name4", OracleTypes.CURSOR);
            stmt.registerOutParameter("output_parameter_name5", OracleTypes.CURSOR);
        
            stmt.execute();
        
            // example of getting an int parameter value
            Integer responseCode = stmt.getInt("output_parameter_name1");

            // example of getting a set of values and mapping them to a List<Account>
            ResultSet accountsRs = (ResultSet) stmt.getObject("output_parameter_name2");
            List<Account> accounts = new ArrayList<>();

            while (accountsRs != null && accountsRs.next()) {
                accounts.add(new Account()
                    .setAccountNumber(accountsRs.getString("STRING"))
                    .setAccountCloseDate(accountsRs.getDate("DATE") == null
                        ? null
                        : accountsRs.getDate("DATE").toLocalDate())
                    .setAccountBalance(accountsRs.getBigDecimal("BIG_DECIMAL")));
            }
        
            //...
        } catch (Exception e) {
            //...
        }
    }
}