javaspringoracle-databasesys-refcursor

Map SYS_REFCURSOR parameter from stored procedure


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");

Solution

  • 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) {
                //...
            }
        }
    }