Too Many Open Cursors

Occasionally, you may find your program encountering "Too Many Open Cursor" when using Oracle.

You may be able to resolve this by:

1. Closing any open resultset.
2. Dispose any database adapter after use.
3. Disposing your database command object after use (either oleDBCommand or OracleCommand) whether or not it is a prepared statement or not.
   

What sometimes happen is after you create a database command within your subroutine and then you perform a query a cursor is opened. We sometimes assume that when we exit the routine, the GC (garbage collector) will automatically close the open cursors and dispose of the objects that are no longer used. However, I found out that it does not always happen. In an environment where you perform a lot of query or update, the system may not know that it can reuse the existing open cursor and will open new cursors during each iteration or call and sooner or later will hit the limit.

This applies whether you are using .Net framework or Java.

I have found this has helped me avoid the "Too Many Open Cursors" issue in both environment.