Re: Too many implicit cursors!?

Setting the OPEN_CURSOR to a larger number doesn't solve the problem in my case. It just makes the problem happen later. As near as I can tell, Oracle is not decrementing the cursor counter when implicit cursors are closed. I tried using explicit cursors for all of my select statements. That helped but, again, the problem just happened later.

Here's a couple of quotes from Oreilly's PL/SQL Programming Manual:

"After the [SQL] statement is executed (whether it is SELECT, UPDATE, DELETE, or INSERT), the implicit cursor will already have been opened and closed implicitly."

"When you close a cursor... the memory for that cursor is released, and any locks caused by the cursor are removed. The RDBMS decreases by one the number of cursors currently open."

The claims these two statements make apparently aren't true for implicit cursors inside packages. If they were true, my package would use at most 2 cursors, because it would be recycling them with every call.

I'm thinking that this is a bug in the way Oracle handles implicit cursors in packages. And since I didn't shell out the big $$ for a support contract, I'm stuck with the lemon I bought.

Anybody have any workarounds?

STAN Stan wrote in message <7gmo5n$j9$1_at_birch.prod.itd.earthlink.net>...
>I'm using Personal Oracle 8.0.4 with PL/SQL on NT. According to the
>documentation I've read, for any SELECT, UPDATE, DELETE, OR INSERT
statement
>oracle opens and closes an implicit cursor automatically.
>
>Nonetheless, I'm getting the dreaded "maximum open cursors exceeded." My
>statements are running inside a package's procedures and functions. I do
>several hundred SELECTS, INSERTS, UPDATES, DELETES within a transaction.
My
>statements are not complex and do not use subqueries.
>
>Apparently, the "automatic closing" doesn't free up the cursors until after
>the commit. I don't understand this. Since you can't use the implicit
>cursor in your program anyway, why does it hang around wasting resources
>until commit or close-connection?
>
>Any suggestions, tips, tricks?
>
>STAN
>
>
>
Received on Tue May 04 1999 - 17:07:45 CDT