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
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.
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.
