ORA-01000: Maximum Open Cursors Exceeded Using Spring SimpleJDBCCall

Question

We use Spring SimpleJdbcCall to call stored procedures in Oracle that return cursors. It seems that SimpleJdbcCall does not close the cursors, and after a while the maximum open cursors are exceeded.

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

There are several other people on the forums who have experienced this, but do not seem to have received answers. It looks like a bug in spring oracle support.

This error is critical and may affect our future use of Spring JDBC.

Does anyone encounter a fix — either tracking a problem with Spring code, or finding a workaround that avoids the problem?

We are using Spring 2.5.6.

Here is a new version of the code using SimpleJdbcCall that does not seem to correctly close the result set that proc returns with the 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") 

An older version of code that does not use Spring JDBC does not 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 appears that Spring JDBC does NOT call rset.close (). If I comment on this line in the old code, then after load testing we will get the same database error.

Answer 1

After much testing, we fixed this problem. This is a combination of how we used the spring framework and the oracle client and oracle DB. We created new SimpleJDBCCalls that used oracle JDBC client metadata calls that were returned as cursors that did not close or clear. I consider this a mistake in the spring JDBC framework in the way it calls metadata, but then does not close the cursor. spring should copy the metadata from the cursor and close it correctly. I am not at a loss to open the problem with jira using spring, because if you use best practice, the error is not displayed.

Fine-tuning OPEN_CURSORS or any other parameter is the wrong way to fix this problem and simply delays it from appearing.

We worked on it / fixed it by moving SimpleJDBCCall to a singleton DAO, so for each processed oracle we call only one cursor. These cursors are open to the life of the application, which I consider a mistake. As long as OPEN_CURSORS is greater than the number of SimpleJDBCCall objects, then there will be no problem.

Answer 2

Well, I have this problem when I read BLOB. The main reason was that I was also updating the table, and the expression containing the update offer did not close automatically. Nasty cursorleak eats all free cursors. After the statement.close () statement is explicitly called, the error disappears.

Morality - always closes everything , do not rely on automatic closure after an order.

Answer 3

Just be careful with setting OPEN_CURSORS to higher and higher values, as there is overhead, and this may just help you use the real problem / error in your code.

I have no experience with the Spring side, but I was working on an application in which we had a lot of problems with ORA-01000 errors, and constantly setting OPEN_CURSORS just made the problem go away for a short while ...

Answer 4

I can promise you that this is not Spring. I worked on the Spring 1.x application, which aired in 2005 and has not been linked to it since. (WebLogic 9., JDK 5). You do not close your resources properly.

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

Answer 5

Oracle OPEN_CURSORS is the key to success. We have a small 24x7 application that works with Oracle XE with several open cursors. We had intermittent max open cursors errors until we set the initialization value OPEN_CURSORS> 300

Answer 6

The solution is not in Spring, but in Oracle: you need to set the initialization parameter OPEN_CURSORS to a value that exceeds the default value of 50.

Oracle - at least like 8i, perhaps it has changed - will process JDBC PreparedStatement objects if you don't leave them open. It was expensive, and most people ended up supporting a fixed pool of open agents that were resubmitted.

(quickly looking through the 10i documents, they clearly note that the OCI driver will cache PreparedStatements, so I assume that the native driver recreates them anyway anyway)