oracle Java不释放oracle游标

Java not releasing oracle cursors

提问by javadeveloper

I'm working on the following code below, (edited for clarity), that is giving me a few problems with open cursors in Oracle. Basically I am trying to select data from the DB and for each row returned there is 0 or more rows of sub data to be selected and appended to the record. This is currently being achieved by calling out to another function while populating the main data set to read the sub data. Which works fine for low volumes of rows, less than 1000. While this is the normal operating range that the users will use it is possible they will request all rows which could be in the order of 10's of thousands of rows. Doing a large volume select results in the ORA-01000: maximum open cursors exceedederror. If I run the code and query v$open_cursors it is possible to see the cursor count clocking up until it falls over.

我正在处理下面的以下代码（为清晰起见进行了编辑），这给我在 Oracle 中打开游标带来了一些问题。基本上我试图从数据库中选择数据，对于返回的每一行，有 0 行或多行子数据要选择并附加到记录中。目前正在通过调用另一个函数同时填充主数据集来读取子数据来实现这一点。这适用于少于 1000 行的少量行。虽然这是用户将使用的正常操作范围，但他们可能会请求所有行，这些行可能是 10 到 10 行的数量级。执行大量选择导致ORA-01000：超出最大打开游标数错误。如果我运行代码并查询 v$open_cursors，则可以看到游标计数一直在计时，直到它倒下为止。

If I comment out the line calling the sub function it works fine, the cursor count in v$open_cursors just fluctuates up and down by a few counts.

如果我注释掉调用 sub 函数的行，它工作正常，v$open_cursors 中的游标计数只会上下波动几个计数。

I noticed that the main function is passing it's connection object through to the sub function, and thought this might be causing the resulting statements and resultsets to stay open while the connection was still open even though they are closed by the code. So I have tried changing the code so each function gets it's own connection from the pool and closes it when done, but this made no difference to the cursors.

我注意到主函数将它的连接对象传递给子函数，并认为这可能导致结果语句和结果集在连接仍然打开时保持打开状态，即使它们被代码关闭。所以我尝试更改代码，以便每个函数从池中获取自己的连接并在完成后关闭它，但这对游标没有影响。

I could up the number of cursors, but a) that just masks the problem, b) I have to stick it up to a stupidly high number to get it to work, and c) I don't want to release flawed code!

我可以增加游标的数量，但是 a) 只是掩盖了问题，b) 我必须把它坚持一个愚蠢的高数字才能让它工作，c) 我不想发布有缺陷的代码！

However I have run out of ideas on how to get the code to release the cursors.

但是，我对如何获取释放游标的代码没有任何想法。

public ArrayList getCustomerSearchResult(Connection con) throws AnException {
    ResultSet rst = null;
    PreparedStatement stmt = null;
    ArrayList resultList = new ArrayList();

    String sql = "----  The search SQL string --- ";

    try {
        stmt = con.prepareStatement(sql);
        rst = stmt.executeQuery();

        while(rst.next()) {
            DataDTO data = new DataDTO();

            data.setSomeData(rst.getString("...."));

            // ##### This call is where the problem lies #####
            data.setSomeSubDataAsAnArrayList(getSubDataForThisRow(data.getId(), con));

            resultList.add(data);
        }

    } catch(Exception e) {
        throw new AnException("Error doing stuff", e);
    } finally{
        try{
          rst.close();
          stmt.close();
          rst = null;
          stmt = null;
        }catch(Exception ex){
            throw new AnException("Error doing stuff", ex);
        }
    }
    return resultList;
}

public ArrayList getSubDataForThisRow(String Id, Connection con) throws AnException {
    ResultSet rst = null;
    PreparedStatement stmt = null;
    ArrayList resultList = new ArrayList();

    String sql = "----  The search SQL string --- ";

    try {
        stmt = con.prepareStatement(sql);
        stmt.setString(1, Id);
        rst = stmt.executeQuery();

        while(rst.next()) {
            SubDataDTO data = new SubDataDTO();

            data.setSomeData(rst.getString("...."));

            resultList.add(data);
        }

    } catch(Exception e) {
        throw new AnException("Error!", e);
    } finally{
        try{
            rst.close();
            stmt.close();
            rst = null;
            stmt = null;
          }catch(Exception ex){
              throw new AnException("Error!", ex);
          }
      }

    return resultList;
} 

采纳答案by Adam Paynter

You could try preparing both the main ("master") and the sub ("detail") statements beforehand:

您可以尝试预先准备主（“主”）和子（“详细”）语句：

PreparedStatement masterStatement = masterConnection.prepareStatement("...");
PreparedStatement detailStatement = detailConnection.prepareStatement("SELECT ... WHERE something = ?");


ResultSet masterResults = masterStatement.executeQuery();
while (masterResults.next()) {
    detailStatement.setInt(1, ...);

    ResultSet detailResults = detailStatement.executeQuery();
    try {
        while (detailResults.next()) {
        }
    } finally {
        detailResults.close();
    }
}

回答by Yishai

JDBC drivers can choke on having multiple result sets on a single connection going at once. I would suspect that this is causing some buggy behavior on Oracle's JDBC driver (I have certainly seen it make issues in others - including just closing the first result set on you, which Oracle obviously isn't doing). I would be much better to get a connection to the header rows, read all of your objects, put them in a Collection, and then iterate back through them and read the detail objects with separate result sets.

JDBC 驱动程序可能会因在单个连接上同时运行多个结果集而窒息。我怀疑这会导致 Oracle 的 JDBC 驱动程序出现一些错误行为（我当然看到它在其他驱动程序中产生问题 - 包括仅关闭您的第一个结果集，而 Oracle 显然没有这样做）。我会更好地连接到标题行，读取所有对象，将它们放入一个集合中，然后遍历它们并读取具有单独结果集的详细信息对象。

Although the JDBC spec doesn't state any obligations on the JDBC driver with regards to this, the JDBC-ODBC bridge explicitly only allows one active statement per connection, so other JDBC drivers are certainly free to have similar restrictions (such as only one open result set per connection).

尽管 JDBC 规范没有规定 JDBC 驱动程序对此的任何义务，但 JDBC-ODBC 桥明确允许每个连接有一个活动语句，因此其他 JDBC 驱动程序当然可以自由地有类似的限制（例如只有一个打开每个连接的结果集）。

回答by fg.

Are you using a connection pool? It may be caching some PreparedStatements when you think you close them.

你在使用连接池吗？当您认为关闭它们时，它可能会缓存一些 PreparedStatements。

To check if you're in this case, try to (temporarily) use non prepared statements or disable the connection pool.

要检查您是否处于这种情况，请尝试（暂时）使用非准备好的语句或禁用连接池。

回答by Billy Bob Bain

Eek, this looks like PowerBuilder code from 1999. Performing multiple selects for the children is an antipattern. You need to do this in fewer calls to the DB... it is way to chatty.

哎呀，这看起来像 1999 年的 PowerBuilder 代码。为子项执行多项选择是一种反模式。您需要以更少的调用次数来执行此操作……这是一种健谈的方式。

Since you are on Oracle, you could try to use a connect by prior to retrieve the child rows with the parent rows - all at once. That's the best solution.

由于您在 Oracle 上，您可以尝试使用 connect by before 检索带有父行的子行 - 一次全部。这是最好的解决办法。

If you can't get the connect by prior, you could combine the calls into an in(id1,id2,...,idN) clause and retrieve them in chunks.

如果之前无法获得连接，则可以将调用组合到 in(id1,id2,...,idN) 子句中并分块检索它们。

Also might take a look at your concurrency settings on the resultset. Maybe you have a scrollable resultset?

还可以查看结果集上的并发设置。也许你有一个可滚动的结果集？

However you solve it, I would be worried about blowing out the VM and getting OOM. You'll need a row limit for the search results.

无论您如何解决它，我都会担心炸毁 VM 并出现 OOM。您需要为搜索结果设置行数限制。