Re: Oracle cursors and purge

[prev] [thread] [next] [Date index for 2004/11/08]

Hi Jim,

do you use retrieve_from_sql or other functions which include SQL  
statements
or parts of SQL statements?

Can you post here a piece of code incl. your sql statement which raised
this exception?

If you don't use bind variables in the WHERE part of your sql statements
and query a large number of objects, Oracle will create an open cursor
for every single call to the db.


Geetings
Adam


On Wed, 3 Nov 2004 09:45:49 -0500, Jim Brandt <cbrandt@xxxxxxx.xxx> wrote:

> I've got a web app using Class::DBI and I recently had an "edge case"  
> for a particular script that caused Oracle to start spewing:
>
> ORA-01000 maximum open cursors exceeded
>
> I investigated and found a few loops creating quite a few CDBI objects  
> because of the large number of records.
>
> I experimented with the purge_object_index_every method, and setting  
> that to a lower value appeared to solve the problem. I think our Oracle  
> cursor setting is at 1000, so the script was going over.
>
> My question is, would folks expect setting the purge this way would  
> solve the problem? I just want to make sure it's actually doing what I  
> think it's doing.
>
> If this is the case, I can add some info to the Kwiki regarding the open  
> cursors error.
>
> Thanks,
> Jim
>
> ==========================================
> Jim Brandt
> Administrative Computing Services
> University at Buffalo
>



        -- 
        -----------------------------------------------------------------------
Adam Przygienda
Computer Science Engineer SEC
SWISSCOM Enterprise Solutions AG
IP-Plus Internet services  http://www.ip-plus.net
-----------------------------------------------------------------------