Subject: ORA-1000 When Running JDBC Application
  Doc ID: Note:1077199.6 Type: PROBLEM
  Last Revision Date: 07-DEC-2006 Status: PUBLISHED

Problem Description
-------------------
 
You have a JDBC application which runs for several hours.  It then
returns the following error:

   ORA-1000 Maximum open cursors exceeded

The open_cursors parameter in init.ora is currently set to a number in
excess of the maximum number of cursors you expect your application
to have open at any one time.  Increasing open_cursors extends the
period of time before failure, but does not resolve the issue.


Solution Description
--------------------
 
Typically, in Java, when an object goes out of scope, it is automatically 
garbage collected, but the Java specification does not place any specific
requirements on the JVM regarding when (or even if) this will occur for any
particular object.  Therefore, do not rely on finalizers to close your 
cursors.

Explicity close all cursors ResultSet.close(), Statement.close(), and
PreparedStatement.close() when you no longer need the cursor.  This ensures
that the corresponding server-side cursors are closed, preventing the ORA-1000
error. Be sure to close these JDBC objects within the scope that they were
created. Closing these objects in a finally{} block is recommended, however,
DO NOT rely upon a finalize() method because a finalize() method is never
guaranteed to run by the JVM!

A common error occurs when CallableStatement is used to call a PL/SQL procedure
that returns a REF CURSOR. If you do not close the corresponding ResultSet object,
a cursor will be left open on the server.
 
You can query the SQL_TEXT column in V$OPEN_CURSOR view from the schema 
to determine which cursors are not being closed.  For example:

    select sql_text from v$open_cursor;


Related Bugs
------------

Bug 1258916 ORA-1000 AFTER DOING REPEATED CALLS TO TRANSACTION_REPEATABLE_READ 
Bug 2348347 USING BLOB TRIM METHOD LEAVES OPEN CURSORS WITH 901, 9012 JDBC DRIVERS 


Reference
---------

Oracle8i JDBC Developer's Guide and Reference, Chapter 3, Basic Features,
First Steps in JDBC, Part Number a90211.

.