title: ORA-01000: maximum open cursors exceededwhen using Spring SimpleJDBCCall
body: <p>We are using Spring SimpleJdbcCall to call stored procedures in Oracle that return cursors. It looks like SimpleJdbcCall isn't closing the cursors and after a while the max open cursors is exceeded.</p>
  
  <pre><code>ORA-01000: maximum open cursors exceeded ; nested exception is java.sql.SQLException: ORA-01000: maximum open cursors exceeded spring 
  </code></pre>
  
  <p>There are a few other people on forums who've experienced this but seemingly no answers. It looks like me as a bug in the spring/oracle support.</p>
  
  <p>This bug is critical and could impact our future use of Spring JDBC.</p>
  
  <p>Has anybody come across a fix - either tracking the problem to the Spring code or found a workaround that avoids the problem?</p>
  
  <p>We are using Spring 2.5.6.</p>
  
  <p>Here is the new version of the code using SimpleJdbcCall which appears to not be correctly closing the result set that the proc returns via a cursor:</p>
  
  <pre><code>...
  SimpleJdbcCall call = new SimpleJdbcCall(dataSource);
  
  Map params = new HashMap();
  params.put("remote_user",  session.getAttribute("cas_username") );
  
  Map result = call
    .withSchemaName("urs")
    .withCatalogName("ursWeb")
    .withProcedureName("get_roles")
    .returningResultSet("rolesCur", new au.edu.une.common.util.ParameterizedMapRowMapper() )
    .execute(params);
  List roles = (List)result.get("rolesCur")
  </code></pre>
  
  <p>The older version of the code which doesn't use Spring JDBC doesn't have this problem:</p>
  
  <pre><code>oracleConnection = dataSource.getConnection();
  callable = oracleConnection.prepareCall(
        "{ call urs.ursweb.get_roles(?, ?) }"    );
  callable.setString(1, (String)session.getAttribute("cas_username"));
  callable.registerOutParameter (2, oracle.jdbc.OracleTypes.CURSOR);
  callable.execute();
  ResultSet rset = (ResultSet)callable.getObject(2);
  ... do stuff with the result set
  if (rset != null) rset.close(); // Explicitly close the resultset 
  if (callable != null) callable.close(); //Close the callable
  if (oracleConnection != null) oracleConnection.close(); //Close the connection
  </code></pre>
  
  <p>It would appear that Spring JDBC is NOT calling rset.close(). If I comment out that line in the old code then after load testing we get the same database exception.</p>