Re: ODBC application getting "ORA-01000: maximum open cursors exceeded"!

Hi Jim,

First of all THANK YOU for your insight!

Binding variables might make sence if I was openening a recordset then doing various opperations on it, but I am not. All I am doing is firing execute sql statements at the database, so I'm not really sure how you would bind varaibles under these circumstances.

Originally I wrote my own wrapper class for ODBC, but I came accross another wrapper class written by someone else, which appeared to be MUCH better written than my quick and dirty class. Anyway I had a look at what I was doing in my old class and what the new wrapper class was doing when it did an SQLExecute. What I found was that the new wrapper class did not seem to tidy up in the way I would have expected (possible for other good reasons). Anyway I have added an additional method using the code from my original wrapper class, which tidies up correctly after the SQLExecute method has been called.

I probable need to do some more testing to make sure everything is still OK, but what I have done, seems to have got shut of the problem, and I am getting what I want, at least for now! :-))

The analogy with allocating memory seems to be an interesting one, something I shall keep in mind.

Anyway MANY THANKS, although what you said didn't initially make a great deal of sence to me, it did at least point me in the right direction.

Cheers,

Martin

"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message news:wAzcd.197890$wV.73603_at_attbi_s54...
>
> "M" <ihatespam.0.a101888_at_spamgourmet.com> wrote in message
> news:_2xcd.79699$ay5.9643_at_fe1.news.blueyonder.co.uk...
>> Hi Folks,
>>
>> I have got an C++ application that is using Oracle as the backend
>> database, using ODBC drivers and BeginTransaction, Commit & Rollback.
>> What
> I
>> do is send loads of Update SQL Statements to the database and when the
> user
>> is ready they commit the changes. The problem I have got, is that when a
>> large number of records are involved eg. 100 I get the following error:
>>
>> ODBC "ORA-01000: maximum open cursors exceeded"
>>
>> Having done some Googling it appears that the maximum default number
> of
>> cursors (presumable 1 cursor / update SQL statement) is only set to 50.
> The
>> only way to get round this seems to be to modify open_cursors=50 in
>> INITORCL.ORA to some other number.
>>
>> Is there a way to modify this number at run time (i.e.
>> Programmatically)?
>>
>> Any help would be most appreciated.
>>
>> I thought the whole point of using an Oracle database is so that it
>> could process LARGE numbers of records! 50 seems to kind of defeat the
> whole
>> object of the exercise! This just seems to be TOTALLY BIZARRE to me!
>>
>> Martin
>>
>>
> You can change the parameter, but it is a static parameter. It is a limit
> per session not database wide. (each session can have at most x number of
> open cursors). You might have an poorly written application and raising
> the
> limit will just delay the problem for a little while. (Like haveing memory
> leaks in an application, you can add more RAM but you would just be
> pissing
> into a bigger pool, not solving the problem.)
>
> Here are my guesses at what is going on:
> 1. If you are not using bind variables (and you should use bind variables)
> then you are opening a cursor for each insert into a table. (and never
> closing it) Don't this is like a memory leak. At hre very least you
> should
> close the cursor when you are done and if you are not going to reuse it.
> 2. You are opening cursors, not closing them and not reusing them. (read
> the
> application programmer's guide)
> 3. You should be using bind variables and reusing the cursor.(for the same
> statement with different bind values) Open the cuursor once only, and re
> use it again and again. Close when exiting the application. Then you can
> set your number once and not worry about it.
> 4. If you use bind variables and are doing a lot of inserts, updates,
> selects, deletes etc. on a table then use the array interface. Much
> faster.
>
> Jim
>
>
Received on Mon Oct 18 2004 - 12:01:11 CDT