Oracle Cursor and SimpleJdbcCall

The title does sound innocent enough, calling a store procedure by using Spring SimpleJdbcCall should be simple and well documented, in usual circumstances this is the case however when Oracle Cursor is involved all hell brakes loose.

An example for such a procedure:

CREATE PROCEDURE GET_COUNTRIES_BY_INDEPENDECE (cursor OUT  types.ref_cursor , date NUMBER) AS 
BEGIN     
  OPEN cursor FOR select * from COUNRTY where INDEPENDENCE >= date;
END;

-- note that the ref_cursor type has to be declared in a package

CREATE OR REPLACE PACKAGE types
AS
   TYPE ref_cursor IS REF CURSOR;
END;

The first problem starts with the fact that the Oracle driver doesn't provide the metadata that Spring requires, this means that the programmer has to define all the in and out parameters by himself and that the metadata depended functionality of Spring has to be disabled (failing to do so make Spring ignore in/out parameter decelerations, verified by Spring source code debugging).

The second problem is how to make Spring work with the result set that the procedure provides via the cursor, i didn't find any mentioning of this on the web (besides some forum entries that didn't show a clear solution path), one tutorial that did help was jdbc related.

After a couple of attempts and Spring source code reading iv managed to find a winning solution:

  ParameterizedRowMapper<Map> mapper = new ParameterizedRowMapper<Map>() {
    @Override
    public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
      BasicRowProcessor processor = new BasicRowProcessor();// commons dbutils 
      return processor.toMap(rs);
    }
  };

 
 final Map in = new HashMap();
 in.put(inParam, value);
 JdbcTemplate template = new JdbcTemplate(dataSource);
 SimpleJdbcCall jdbcCall = new SimpleJdbcCall(template).withProcedureName("GET_COUNTRIES_BY_INDEPENDECE").withCatalogName("CATALOG");

 // taking care of the cursor result set with a custom mapper
 jdbcCall.returningResultSet("cursor", mapper);

 // disabling metadata so that Spring won't reject parameter declaration
 // this is due to the lacking oracle jdbc driver 
 jdbcCall.withoutProcedureColumnMetaDataAccess();

 // order matters! spring uses them according to the position in the collection  
 jdbcCall.addDeclaredParameter(new SqlOutParameter("cursor",OracleTypes.CURSOR));
 jdbcCall.addDeclaredParameter(new SqlParameter(someDateInMili,OracleTypes.NUMERIC));

 List<Map<String, Object>> result = (List<Map<String, Object>>) jdbcCall.execute(in).get("cursor");// getting back the result set that was provided by the cursor

There are a couple of weird things in this code, first we must declare not only that the call returns a result set but also an out parameter for the cursor, second the order of the deceleration matters! (which makes sense since Spring can't guess the placement of the parameter without metadata).
Thats about it, hopefully iv saved you some time ;)