• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Cursor leak

 
Ranch Hand
Posts: 75
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Sheriff
Posts: 3063
12
Mac IntelliJ IDE Python VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 75
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 75
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Put a gun against his head, pulled my trigger, now he's dead, that tiny ad sure bled
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic