ORA-01000：使用Spring SimpleJDBCCall时超出最大打开游标数 [英] ORA-01000: maximum open cursors exceededwhen using Spring SimpleJDBCCall

问题描述

我们使用Spring SimpleJdbcCall来调用Oracle中返回游标的存储过程。看起来SimpleJdbcCall没有关闭游标，一段时间后超出了最大打开游标。

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 

有一个在论坛上很少有其他人经历过这个，但似乎没有答案。看起来我是spring / oracle支持中的一个bug。

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.

这个bug很关键，可能影响我们将来使用Spring JDBC。

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

是否有人遇到过修复 - 要么将问题跟踪到Spring代码，要么找到避免问题的解决方法？

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

我们正在使用Spring 2.5.6。

We are using Spring 2.5.6.

这是使用SimpleJdbcCall的代码的新版本，似乎没有正确关闭proc通过游标返回的结果集：

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

旧版本不使用Spring JDBC的代码没有这个问题：

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

看来Spring JDBC没有调用rset.close（）。如果我在旧代码中注释掉那一行，那么在加载测试之后我们得到相同的数据库异常。

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.

推荐答案

经过多次测试我们解决了这个问题。它是我们如何使用spring框架和oracle客户端以及oracle DB的组合。我们正在创建新的SimpleJDBCCalls，它们使用oracle JDBC客户端的元数据调用，这些调用作为游标返回，而这些游标未被关闭和清理。我认为这是Spring JDBC框架中一个如何调用元数据然后不关闭游标的错误。 Spring应该从光标中复制元数据并正确关闭它。我没有打算用spring打开jira问题，因为如果你使用最佳实践，那么bug就不会出现。

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.

调整OPEN_CURSORS或任何其他参数是错误的修复此问题的方法只是延迟它出现。

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

我们通过将SimpleJDBCCall移动到单个DAO来解决它/修复它所以只有一个游标打开我们称之为的每个oracle proc。这些游标在应用程序的生命周期内是开放的 - 我认为这是一个错误。只要OPEN_CURSORS大于SimpleJDBCCall对象的数量，就不会有麻烦。

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.

这篇关于ORA-01000：使用Spring SimpleJDBCCall时超出最大打开游标数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！