Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Too many implicit cursors!?

Re: Too many implicit cursors!?

From: Stan <sbrubaker_at_earthlink.net>
Date: Tue, 4 May 1999 19:33:54 -0600
Message-ID: <7go76c$91u$1@fir.prod.itd.earthlink.net>


Thanks for your input. I tried setting the CLOSE_CACHED_OPEN_CURSORS parameter to TRUE. It didn't have an affect on my problem. The only way I've been able to get this to work is to open/close one database connection per transaction. But that's painfully slow.

The reference manual suggests viewing the current parameters with the Server Manager. Since I'm using Personal Oracle, I don't think I get a server manager (at least, I find no evidence of it on the CD or the installation).

I know I'm editing the correct "Init<sid>.ora" file because the OPEN_CURSORS value has an affect. And I know that commits are taking place, because data is being added to my database for the few dozen transactions that happen before I run out of cursors.

I wonder if I'm just running into a limitation in Personal Oracle. I registered for the Oracle 8i Developer's CD, maybe I'll have more luck with that (if and when it gets here).

Thanks again for your suggestions,

STAN Thomas Kyte wrote in message <37318237.34857051_at_192.86.155.100>...
>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 - 20:33:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US