Clarification of cursors in oracle with jdbc?

Quite long time ago I've encountered a similar problem. As far as I remember, the issue was in delayed garbage collection. Database cursor won't close until garbage collector finds and releases the appropriate object.

If statements are created frequently, you can run into this issue. Try to invoke garbage collector manually from time to time.

Up vote 2 down vote favorite share g+ share fb share tw.

I have situation where a 3rd party open source product I am using is running out of cursors in Oracle and receiving the error: java.sql. SQLException: ORA-01000: maximum open cursors exceeded My maximum cursors is set to 1000 and I am trying to figure out if the code that is reaching this limit is doing something incorrectly, or if I simply need to increase my limit. After some investigation I found a point in the code at which a ResultSet is created, thereby increasing my open cursor count by 1.

However, that ResultSet is soon closed after use.... BUT the cursor count remains where it is. I was able to reproduce the logic in a simple JDBC application outside of the 3rd party open source project. Package gov.nyc.doitt.

Cursor; import java.sql. Connection; import java.sql. DriverManager; import java.sql.

PreparedStatement; import java.sql. ResultSet; import java.sql. SQLException; public class CursorTest { public static void main(String args) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { Class.

ForName("oracle.jdbc.driver. OracleDriver"); conn = DriverManager. GetConnection("jdbc:oracle:thin:@myhost:1537:mydb", "username", "password"); // as expected: there are 0 cursors associated with my session at this point ps = conn.

PrepareStatement("select my_column from my_table where my_id =? "); ps. SetInt(1, 86); // as expected: there are 0 cursors associated with my session at this point rs = ps.executeQuery(); // opens 1 cursor // as expected: there is 1 open cursor associated with my session at this point } catch (Throwable t) { t.printStackTrace(); } finally { // as expected: there is 1 open cursor associated with my session at this point try { rs.close(); } catch (SQLException e) { System.err.

Println("Unable to close rs"); } // not expected: there is still 1 open cursor associated with my session at this point try { ps.close(); } catch (SQLException e) { System.err. Println("Unable to close simplePs"); } // not expected: there is still 1 open cursor associated with my session at this point try { conn.close(); } catch (SQLException e) { System.err. Println("Unable to close conn"); } // as expected: at this point my session is dead and so are all the associated cursors } } } I found some Oracle documentation that made me think that all open cursors would be closed if you closed our ResultSet and PreparedStatements, but my open cursors seem to be hanging around.

See this FAQ (http://download.oracle.com/docs/cd/B10501%5F01/java.920/a96654/basic.htm#1006509) which says "Closing a result set or statement releases the corresponding cursor in the database. " Only based on my test that doesn't seem to happen, so I must be lacking some basic understanding. Can anyone explain how Oracle handles cursors or point me to some documentation that will enlighten me?

Thanks! Java oracle jdbc cursor resultset link|improve this question asked Dec 14 '09 at 19:46Sarah Haskins25919 90% accept rate.

The code looks fine I think. Are you using the driver provided by oracle itself. – Rajat Dec 14 '09 at 20:13 Your code looks perfectly OK.

– ammoQ Dec 14 '09 at 20:19.

Quite long time ago I've encountered a similar problem. As far as I remember, the issue was in delayed garbage collection. Database cursor won't close until garbage collector finds and releases the appropriate object.

If statements are created frequently, you can run into this issue. Try to invoke garbage collector manually from time to time: Runtime r = Runtime.getRuntime(); r.gc(); just to check this supposition.

1 If I move all of the PreparedStatement and ResultSet logic into separate method, then the cursors go away after the PreparedStatement and ResultsSet are closed, but before the connection is closed (in otherwords, once they are out of scope). Thanks! – Sarah Haskins Dec 14 '09 at 20:54 This more sounds like a bug in the JDBC driver used.

If you can, upgrade the JDBC driver to the latest compatible version available. This is preferred above changing the JDBC coding to fit an implementation-specific (mis)behaviour. – BalusC Dec 14 '09 at 21:10 I am using the latest driver.

This is just test code to help me understand how it works, so no coding changes are being made to accommodate anything. – Sarah Haskins Dec 15 '09 at 14:15.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.