ORA-01000: maximum open cursors exceededwhen using Spring SimpleJDBCCall

Question

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.

ORA-01000: maximum open cursors exceeded ; nested exception is java.sql.SQLException: ORA-01000: maximum open cursors exceeded spring 

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.

This bug is critical and could impact our future use of Spring JDBC.

Has anybody come across a fix - either tracking the problem to the Spring code or found a workaround that avoids the problem?

We are using Spring 2.5.6.

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:

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

The older version of the code which doesn't use Spring JDBC doesn't have this problem:

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

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.

Answer 1

After much testing we have fixed this problem. It is a combination of how we were using the spring framework and the oracle client and the oracle DB. We were creating new SimpleJDBCCalls which were using the oracle JDBC client's metadata calls which were returned as cursors which were not being closed and cleaned up. I consider this a bug in the Spring JDBC framework in how it calls metadata but then does not close the cursor. Spring should copy the meta data out of the cursor and close it properly. I haven't bothered opening an jira issue with spring because if you use best practice the bug isn't exhibited.

Tweaking OPEN_CURSORS or any of the other parameters is the wrong way to fix this problem and just delays it from appearing.

We worked around it/fixed it by moving the SimpleJDBCCall into a singleton DAO so there is only one cursor open for each oracle proc that we call. These cursors are open for the lifetime of the app - which I consider a bug. As long as OPEN_CURSORS is larger than the number of SimpleJDBCCall objects then there won't be hassles.

Answer 2

Well, I've got this problem when I was reading BLOBs. Main cause was that I was also updating table and the Statement containing update clause was not closed automatically. Nasty cursorleak eats all free cursors. After explicit call of statement.close() the error disappears.

Moral - always close everything, don't rely on automatic close after disposing Statement.

Answer 3

Just be careful setting OPEN_CURSORS to higher and higher values as there are overheads and it could just be band-aiding over an actual problem/error in your code.

I don't have experience with the Spring side of this but worked on an app where we had many issues with ORA-01000 errors and constantly adjusting OPEN_CURSORS just made the problem go away for a little while ...

Answer 4

I can promise you that it's not Spring. I worked on a Spring 1.x app that went live in 2005 and hasn't leaked a connection since. (WebLogic 9., JDK 5). You aren't closing your resources properly.

Are you using a connection pool? Which app server are you deploying to? Which version of Spring? Oracle? Java? Details, please.