ORA-01000: maximum open cursors exceed

Just to add a few things from some of my personal experiences with this same issue...

As schwertner said, and I can't stress this enough, you should ALWAYS, ALWAYS explicitly close your ResultSets and Statements when they are no longer needed.  Don't rely on simply closing the Connection, it's not safe.  

Another thing to note is that in your Exceptions, make sure that you are properly closing your ResultSets and Statements (as well as your Connections if need be).

Lastly, when reusing ResultSets and Statements, you should always explicitly close them prior to reinitializing them.  If you simply reinitialize them, though the reference to it may be gone, it will still remain in memory. Hence you will run the risk of getting the max open cursors Exception.

Two comments:

COMMENT #1:
The following code snippet is always a good coding pattern to follow when using Statement, PreparedStatement and ResultSet.
--------------

Connection conn = getConnection();
PreparedStatement ps = null;
ResultSet rs = null;

try {
  ps = conn.prepareStatement(...);
  rs = ps.execute...

}
catch (SQLException sqe) {
  /* should I handle it? */
}
finally {
  if (rs != null) try { rs.close(); } catch (SQLException) { /* should I handle it? */ }
  if (ps != null) try { ps.close(); } catch (SQLException) { /* should I handle it? */ }
}

Thus, regardless of what happens, you will always correctly close the ResultSet and PreparedStatement. This of course only works if you don't need to pass the ResultSet back and can do all your work within the try {} block.


COMMENT #2:
I recently had a colleague who was using the Oracle 8.1.7 drivers against an 8.1.5 database. His long running process was verified to not have resource leaks (he was closing everything correctly) however he routinely obtained cursor errors.

Upgrading to the Oracle 8.1.7 database fixed the problem. We did not try downgrading the drivers or verifying the V$OPEN_CURSORS.

Now I have checked my code and made sure that all the connections, resultset and Statements are closed after being used. I tried select count(*) from v$open_cursor; and it keeps incrementing (never goes down) once I start the application. I thought if I am closing the connections, statements and resultset properly the number of open cursor will not increase cause once I'm done using them I release them.

I've tried closing all result sets before. And I still got the max cursors exceeded errors. As mentioned, bumping up the cursor
limit is only a temporary solution. There is only one solution that I found - that is to close your connections periodically.
When a connection is closed, all the associated resources (such as cursors) are also closed.

I've experimented a bit
        with this - you can set an arbitrary "chunk" size. When you hit this chunk size you can close all connections and
        restart. When you close a connection, it will close all associated resultsets, cursors etc which will free up some
        resources.
            eg.
       // Loop counter
       int count = 0;
       // To indicate when to clean up connection and start afresh
       int chunks = 20; // although this size is aribtrary, but found to be a good setting
 
       for (int i=0; i<100;i++)
       {
        count++;
        if ((count % chunks) == 0)
        {
             if (conn != null && !conn.isClosed()) conn.close();
             conn = ConnectionManager.connect(database_url_string);
        }  
       }

Hope that helps.