Please help as am facing the following problem with an Oracle database. A Connection to the database is opened and PreparedStatements are created to insert into the database. As there is an idle timeout defined on the database, the Connection is lost and I need to reconnect to the database. The code I have goes like this:-
In spite of closing the PreparedStatements, the application throws an error "ORA-01000: maximum open cursors exceeded", when the connection is lost and we try to recreate the PreparedStatements. Any pointers to fix this will be appreciated. Thanks, Venita. [ March 03, 2003: Message edited by: Venita Glasfurd ]
Hi Venita, When using the Oracle database and the Oracle JDBC (thin) driver, you must close "ResultSet" objects _before_ closing "PreparedStatement" objects. From the code you have posted, it seems that you are closing the "PreparedStatement"s first, and then the "ResultSet" -- which raises the question, 'why is there a collection of "PreparedStatements" but only one "ResultSet"?' A "ResultSet" object is associated with exactly one "[Callable/Prepared]Statement". Looks like your code may be "leaking" Oracle cursors in any event (regardless of whether you have lost the database connection, or not). In my experience, using Oracle 220.127.116.11 database with the Oracle (thin) JDBC driver on SUN [sparc] Solaris 7 and J2SE version 1.3.1, if you close the "PreparedStatement" before (or without) closing the "ResultSet", the (Oracle) cursors remain open! I think you may need to refactor your code! Hope this has helped you. Good Luck, Avi.
Hi Venita, Try closing the prepared statement. In JDBC, preparedStatement.close() closes the cursor. Still, if the problem persists, change the initialization parameter OPEN_CURSORS, restart the instance and have a look. You can query the v$parameter to have a look at the number of open cusrors.
Closing a statement should close the ResultSet(s) associated with it. However, especially with the Oracle driver you must close your statements with the utmost discipline. And if that close() statement is not in a finally clause, you have a resource leak on your hands! If you're using connection pooling as well, this gets even worse -- if you have a leak somewhere, no matter how small, you will get the ORA-1000 problem. - Peter
Kirtikumar and Peter, What version of the Oracle database and JDBC driver and JRE, together, demonstrates the behaviour you describe? I have verified the behaviour I have described (in my environment -- which I detailed in my previous reply). A co-worker has informed me that there is a technical note on Oracle's "MetaLink" Web site: http://metalink.oracle.com that also verifies the behaviour I am seeing. PLEASE tell me what combination I need, in order to get the behaviour you are describing! Thanks (in advance :-), Avi.
Peter den Haan
Joined: Apr 20, 2000
Avi, The fact that closing a Statement closes its ResultSet (or, in JDBC 3.0, ResultSets) is documented in the JDBC API javadoc and in the JDBC specification (section 13.1.3 for v3.0). Without this, a driver will never get through Sun's JDBC test suite or J2EE certification. My experience is that this mandated behaviour is implemented correctly in the Oracle JDBC driver -- the most telling example is a JDK 1.3.1 web project over an Oracle 8.1.7 EE database I did a few months ago (using release 2 of the thin driver); the code completely relies on this behaviour. Had been any cursor leak I would've noticed as the thing has been scoped and load tested for 30M hits/month. At such sustained loads the smallest leak will cause everything to collapse in a big heap sooner rather than later (obviously, not every page hit will hit the database but it's enough to keep a 4-CPU/4GB box nicely occupied). - Peter [ March 06, 2003: Message edited by: Peter den Haan ]
Joined: Mar 04, 2003
Hi Avi, In my post, I am referring to Oracle 8i, JRE 1.3.0 and JDBC 1.3. I fully agree with Peter when he says:
However, especially with the Oracle driver you must close your statements with the utmost discipline. And if that close() statement is not in a finally clause, you have a resource leak on your hands!
We had a tough time on our previous project with "OPEN_CURSORS", which was developed using Oracle 8i, jdk 1.2.2, and JDBC 1.2 API. The problem was ultimately solved to a great extent when we closed the Statement and the Connection objects in the finally clause of all our servlets. Rgds, Kirtikumar Puthran
is anything else using the database? I had this problem last week & after looking at V$OPEN_CURSOR there were only 200 or so open (10 from my application, i.e. the connection pool). So I assumed someone elses job had eaten them all up. what does, select sql_text, count(sql_text) from v$open_cursor group by sql_text; tell you?
Joined: Mar 04, 2003
select sql_text, count(sql_text) count from v$open_cursor group by sql_text order by count; The above query will return or show you all the open cursors. Rgds, Kirtikumar Puthran
I am running into this at the present time. I am closing resultset, preparedstatement and connection in my finally block. I am processing a spreadsheet, and for each row doing some DB lookups. No big deal, rather similar to a batch program. However, perhaps this is a 'best practices' question or issue. My queries are simply static methods in a class of queries required for processing. Each static method creates a connection, runs the query and closes the resources. This is a web app using connection pooling.