ORA-01000 max cursors exceeded

Hi

My app is written using the OCI running against a 9i 9.2.0.1.0 server running on Windows XP SP2.

I (& my users) keep on encountering the hoary old ORA-01000 problem. The stored procedures in the database use a lot of ref cursors to return result sets. Where possible, in the stored procedures, I close the cursors and where they are returned to the application I have tried a couple of things:

1. I have made my cursors scrollable and then cancelled them by calling OCIStmtFetch2 with nrows set to 0.
2. I have made my cursors non scrollable and always loop through fetching til I hit 1403 (NO_DATA), which, according to the documentation (OCI), implicitly cancels the statement handle.

Either way I still have the cursors I returned from PL/SQL stored procedures hanging around AND I have the implicit cursors PL/SQL opens from standard SQL queries.

If I query v$mystat for the number of open cursors it always seems quite low. I understand that PL/SQL caches its implicit cursors in such a way that they shouldn't be affecting my overall cursor count so I just don't understand why I keep running out!

The application communicates with the database almost exclusively by using stored procedures, so I feel it must be something I am not doing in the OCI code which isn't freeing up the server side cursor resource.

It seems as though the web is full of people experiencing this problem but there are very few useful OCI based responses so I hope someone can help!

Thanks
Alex Received on Wed Feb 09 2005 - 08:31:02 CST