aspose file tools*
The moose likes JDBC and the fly likes Cursor leak Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Cursor leak" Watch "Cursor leak" New topic
Author

Cursor leak

Emil Jennings
Ranch Hand

Joined: Jul 09, 2010
Posts: 48
I've been trying to find a solution for this and have yet to find it so I figured I'd ask...

I have code like this:



The above will not close the open cursors. From what I've read, to include the Oracle docs troubleshooting section, the solution is to close the statement and the cursors will be released. However, from what I've experienced this is not true. Others sources state to also close the ResultSet, but the executeBatch does not return a ResultSet. The only way I've found to release the cursors is to close the connection to the database and create a new one. However, if a Java class is executed within Oracle and it uses jdbc:default:connection, that connection cannot be closed and re-established, resulting in max cursors open exception.

Is there a way to release the cursors without closing the connection? I know I'm not the first person to think of this but the answer is definitely evading me. TIA.
Greg Charles
Sheriff

Joined: Oct 01, 2001
Posts: 2851
    
  11

Normally, you'd want to close both the statement and the connection, and do it in a way that an unexpected execution flow, like an exception, cannot bypass the closing. In the code you've posted, you only close the statement immediately before opening another one. How does the last one get closed? Also, what would happen if executeBatch() threw an exception? Where do you close the connection? How would that be affected by an exception?

What that all means is you should have something like a try-finally structure, with resources opened in the try block, and closed in the finally block. If you're using Java 7, you can use the new try-with-resources structure instead.
Emil Jennings
Ranch Hand

Joined: Jul 09, 2010
Posts: 48
Hey Greg,

The whole thing is in a try/catch/finally block, the final statement is closed in the finally block. There is a connection close in the finally block, but when this is run within Oracle using jdbc:default:connection I don't think the connection is closed because the max cursor error is thrown. I just wanted to keep the code sample short in order to focus on where the problem appears to be occurring.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Are you using a Java stored procedure? How long (how many executions of the code you've posted) does it take before the error pops out? Aren't there any other errors being thrown? Are there other processes active in the database at that time?

Please post your full code including the finally blocks, as this way we're all just guessing.

A few notes:

I wouldn't use statement batching in this case. As far as I know, there is nothing to gain from batching procedure calls, as there are no network roundtrips - your entire code runs in the database. It would make sense to batch insert statements in your situation - Oracle uses array insert internally in this case - but I'm not really aware of any gain for procedure calls. Don't use batches, it will make your code simpler.

Resultsets need to be closed only when the statement creates one. Your statement doesn't create a resultset, regardless of whether you batch them or not.

You do not need to close and prepare the statement again after a commit, CallableStatement will live through a commit. Furthermore, you should not commit in the loop; frequent commits decrease performance in Oracle.
Emil Jennings
Ranch Hand

Joined: Jul 09, 2010
Posts: 48
The code is basically:



Yes, it is a Java stored procedure, there are a few other processes running. It's the cs = connection.prepareCall("{call procedureFoo(?)}"); statements that cause the error (there are no other errors). Each key sent to Foo creates a new cursor and they never release unless I close the connection.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Emil Jennings wrote:The code is basically:

Basically, but not exactly, right? For example, the cs.close() and conn.close() statements in the finally clause throw SQLException, but it is neither caught nor declared. So, this is not the code you're actually running. And until we see the full code (including how you handle these exceptions), I'm not convinced there are no other exceptions thrown by your code. Remember, the devil is in the detail.

Just to clarify: I tend to believe there's something wrong with your code, rather than assume it is Oracle's problem. The best you could do in this situation would be to create a SSCCE that everybody (everybody with Oracle) could run to see the problem. It is very probable that in the process of creating the SSCCE (which, admittedly, can be a time consuming task) you'll discover the error.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Cursor leak