Re: How do I close cursors?

I am also reporting on the outcome of our similar problem. We too were closing statements and result sets, but still received the ORA-01000 too many open cursors error. We discovered that in several cases, we were not closing our statements and result sets "within scope". We opened the cursor in one function, called another function, then closed the statement and result set. We cleaned up our application code to close all statements and result sets in the same function that opened them.

We also discovered, that the v$open_cursor view only reports VALID, INSTANTIATED open cursors. In our case, the cursors were closed "out of scope". They did not appear in v$open_cursor, but were being reported in the v$sesstat and v$sysstat views for statistic #3 (current opened cursors) which reports all open cursors, regardless of their status. Therefore, we were continuing to receive the ORA-01000 error even though v$open_cursor did not report any open cursors. We could not see the open cursors, because we had been instructed to look at the v$open_cursor view, and not the v$sesstat or v$sysstat views.

Lessons learned:

1. Close all statments and result sets "in scope".
2. Use v$sesstat and v$sysstat, in addition to v$open_cursor to monitor ALL open cursors (valid and invalid)

In article <7ldsq0$ng5$1_at_news1.bu.edu>,   tasos_at_csa.bu.edu (Anastasios Kotsikonas) wrote:
> Since I was the creator of the thread I feel I need to report on my
> fingings, based on the suggestions here.... We were indeed closing
> statements, and we indeed had to increase the number of max cursors.
> In addition, we had to put try/catch/finally and close the statements
> in the finally clause.
>
> Thanks Thomas and the others for all the help.
>
> tasos
>

--
Carol Kuczborski
EDS/DEIS II Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Jul 19 1999 - 15:41:59 CDT