We're calling the pl/sql saved procedure through Spring SimpleJdbcCall, the fetchsize set around the JdbcTemplate has been overlooked by SimpleJdbcCall. The rowmapper resultset fetch dimensions are set to 10 despite the fact that we've set the jdbctemplate fetchsize to 200. Any idea why this occurs and just how to repair it?

Have printed the fetchsize of resultset within the rowmapper within the below code snippet - Once it's 200 and other time that it is 10 despite the fact that I personally use exactly the same JdbcTemplate on occassion.

This direct execution through jdbctemplate returns fetchsize of 200 within the row mapper

    jdbcTemplate = new JdbcTemplate(ds);
    jdbcTemplate.setResultsMapCaseInsensitive(true);
    jdbcTemplate.setFetchSize(200);

    List temp = jdbcTemplate.query("select 1 from dual", new ParameterizedRowMapper() {
        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            System.out.println("Direct template : " + resultSet.getFetchSize());
            return new String(resultSet.getString(1));
        }
    });

This execution through SimpleJdbcCall is definitely coming back fetchsize of 10 within the rowmapper

jdbcCall = new SimpleJdbcCall(jdbcTemplate).withSchemaName(schemaName)
                .withCatalogName(catalogName).withProcedureName(functionName);
jdbcCall.returningResultSet((String) outItValues.next(), new        ParameterizedRowMapper<Map<String, Object>>() {
                public Map<String, Object> mapRow(ResultSet rs, int row) throws SQLException {
                   System.out.println("Through simplejdbccall " + rs.getFetchSize());
                    return extractRS(rs, row);
                }
            });
outputList = (List<Map<String, Object>>) jdbcCall.executeObject(List.class, inParam);

If you wish to limit the amount of rows coming back with Oracle, you need to use a subselect query and specify the stop and start rownumber such as this:

SELECT * FROM (SELECT ROWNUM as id, demo.* FROM DEMO_TABLE demo)
WHERE id >= startRowNumber AND id <= stopRowNumber; 

If you do not want any Oracle specific code, you should look at JPA rather than JDBC:

Query selectQuery = entityManager.createQuery(queryString);
selectQuery.setMaxResults(maxNumberOfElements);
selectQuery.setFirstResult(startRowNumber);
List demoList = entityManager.getResultList(queryString);

I am unsure just how much the getFetchSize() method on ResultSet could be reliable. JdbcTemplate calls setFetchSize() around the Statement, and assumes the JDBC driver is going to do the best factor by using it, including propagating it to any or all ResultSet objects. It appears the Oracle JDBC driver is not, within this situation.

The main reason you receive different behavior in the two approaches is the fact that performing an easy SELECT through JdbcTemplate is straightforward JDBC, whereas the ResultSet you receive back from SimpleJdbcCall is acquired being an OUT parameter towards the call. The second is much more complex, also it appears the details are being lost.

Like a workaround, perhaps you have attempted calling ResultSet.setFetchSize() yourself? May possibly not work, but it is worth a try. You might submit a problem to http://jira.springsource.org/ to ascertain if they believe that Spring's JDBC layer could address it transparently.

Not really a direct answer, but a useful factor I believe. Whenever you create objects of spring classes manually many people often forget calling the afterPropertiesSet() method (that is known as by Spring to do any initialisation).

I checked the jdbcTemplate.afterPropertiesSet() it only appears to create the exception translator and also the databases.