Oracle Cursors

I have been tracking down an issue dealing with open cursors in an application I am working on lately. I have gone through the code numerous times and saw that we were closing all of the results sets that we manage (meaning those outside of the Hibernate Criteria/List) and also that we were closing the Hibernate Sessions/Connections.

I went digging through the code and looking specifically at the code where we deal directly with the DB one (basically non-hibernate calls). I did not find anything out the ordinary so after much surfing and experimenting I came across a posting about the difference between what the Java Spec says and how most JDBC drivers actually implement things. Based on that I found that Oracle recommends when opening a callable statement that you close the result set, callable statement and connection (connection only if needed by the code/logic) to ensure that things are cleaned up properly.

I honestly did not believe this and thought it was very strange as I believed that the spec saying closing the connection was good enough. So based on this I ran some tests with our DBA then made the necessary changes and reran the tests again things looked good. So if you are dealing with the cursors and callable statements make sure you are closing all three items to ensure proper clean-up.

There is still a chance for some cursors to remain until they are garbage collected but doing this step ensures that everything is closed properly and in a ready state when this happens. I have also learned that the session statistic 'currently open cursors' can include some cursors that the application has closed. When application code calls for a cursor to be closed, Oracle actually marks the cursor as "closeable". The cursor may not actually be closed until Oracle needs the space for another cursor.