2

What type of resource leak can I expect if ResultSet is closed, but PreparedStatement not. It is very suspicious that it can cause an open cursor issue...

   PreparedStatement p = connection.prepareStatement(...);
    try {
      ResultSet r = p.executeQuery();
      try {
        while (r.next()) {
          ....
        }
      } finally {
        try {
          r.close();
        } catch (SQLException e) {
        // log this or something -- prevent these from masking original exception
        }
      }
    }

It is Oracle 11g, jdbc 11.2.0.3

Thanks

Plz try to answer my question and not focusing on the fix

HamoriZ
  • 2,370
  • 18
  • 38

2 Answers2

5

What type of resource leak can I expect if ResultSet is closed, but PreparedStatement not ?

The leak will be the maximum open-cursor issue.

ORA-01000: maximum open cursors exceeded

If the maximum open cursors is exceeded, the database will become unusable, except for the cursors that are already held. However, most of the times when this happen the held cursors are not even used (which would be the case in your question).

Since java 7, the best way to handle this is to use a try with ressources. Both ResultSet and PreparedStatement implements AutoCloseable interface which mean they will be closed when not needed anymore.

If you don't have java 7, then you need to handle this in the finally block, but make sure you verify for null values before closing else you may encounter an NPE if ressource was never initialized.

Note that the reverse won't cause any problem as closing a statement will automatically close it result set.

Note that you can easily allow more cursors to your application for less chance of exceeding the maximum.

ALTER SYSTEM SET open_cursors = 400 SCOPE=BOTH;

However, most of the time if you encounter maximum open cursors this should not be a solution as it would only hide the real problem.

Jean-François Savard
  • 20,626
  • 7
  • 49
  • 76
  • @HamoriZ The resource leak will be the cursors, as I say in the first line of my answer. – Jean-François Savard Mar 10 '15 at 16:38
  • 1
    Yes sorry, I skipped it somehow :-) Are you sure that not closing the PreparedStatement can cause it even if the the ResultSet is closed? If I do not closethe ResultSet, then it is clear that I will get the cursor error. – HamoriZ Mar 10 '15 at 16:40
  • @HamoriZ Yes, a prepared statement will hold the cursor. However, clausing the prepared statement will automatically close the result set. – Jean-François Savard Mar 10 '15 at 16:44
1

Probably in Oracle you do not risk too much on db server side. Prepared statements are shared in library cache and when statement is not being executed, then Oracle can get rid of it.

But anyway it is very bad habit. It's also questionable what happens on JDBC driver side. This is an excerpt from OCI docs, but I assume that JDBC behavior will be similar:

Statement Caching without Session Pooling in OCI

Users perform the usual OCI steps to logon. The call to obtain a session will have a mode that specifies whether statement caching is enabled for the session. Initially the statement cache will be empty. Developers will try to find a statement in the cache using the statement text. If the statement exists the API will return a previously prepared statement handle, otherwise it will return a newly prepared statement handle.

So it looks like Oracle can avoid even soft-parsing, when PreparedStatement cache is used on client side. So when you do NOT close PreparedStamement you might have problems with this cache.

Note: the mapping is RecultSet => "open cursor", while PreparedStatement probably maps onto an entry in Library cache. For Oracle closing of ResultSet(s) is crucial.

PS: on a contrary on Informix(PostgreSQL and on other RDBMSs) execution plans are not shared and every session process/thread holds it's own set of prepared statements. So when using some other db you could get into serious troubles.

ibre5041
  • 4,903
  • 1
  • 20
  • 35