Oracle Error: ORA-01000 Maximum open cursors exceeded

Resolving The Problem

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 cursors. A finalizer is called when a class is garbage collected.

Explicitly close all cursors (for example ResultSet.close() and Statement.close()) when the cursors are no longer needed. This ensures that the corresponding server-side cursors are closed, preventing the ORA-01000 error.

The SQL_TEXT column can be queried in V$OPEN_CURSOR view to determine which cursors are not being closed. For example:

select sql_text from v$open_cursor;

Notes:

1. Typically, close() statements should be put in a finally clause of the try block.
2. There are known problems in certain versions of Oracle 8.1.6 JDBC drivers that can make this problem worse. The Oracle 8.1.7 JDBC driver or a later version should be used.