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.

Explicitly close all cursors by either using ResultSet.close(), Statement.close(), and/or 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:

SQL> select sql_text from v$open_cursor;