Re: Too many implicit cursors!?

A copy of this was sent to "Stan" <sbrubaker_at_earthlink.net> (if that email address didn't require changing) On Tue, 4 May 1999 16:07:45 -0600, you wrote:

>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.
>

see the init.ora parameter CLOSE_CACHED_OPEN_CURSORS

CLOSE_CACHED_OPEN_CURSORS specifies whether cursors opened and
cached in memory by PL/SQL are automatically closed at each COMMIT. A value of FALSE signifies that cursors opened by PL/SQL are held open so that subsequent executions need not open a new cursor. If PL/SQL cursors are reused frequently, setting the parameter to FALSE can cause subsequent executions to be faster.

A value of TRUE causes open cursors to be closed at each COMMIT or ROLLBACK. The cursor can then be reopened as needed. If cursors are rarely reused, setting the parameter to TRUE frees memory used by the cursor when the cursor is no longer in use.

Parameter type: boolean
Parameter class: static
Default value: FALSE
Range of values: TRUE/FALSE

>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."
>

its logically opened and closed yes.

>"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."
>

memory is reduced if the above parameter is set to false. locks are released, yes.
the rdbms doesn't decrease the count unless the above parameter is set to true.

>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?
>

check out the server concepts manual from cover to cover.

read the server reference manaual from cover to cover, become familar with the parameters you can set.

>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
>>
>>
>>
>
>

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue May 04 1999 - 18:48:06 CDT