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

From: M <ihatespam.0.a101888_at_spamgourmet.com>
Date: Tue, 19 Oct 2004 17:09:42 GMT
Message-ID: <qTbdd.84435$ay5.7530@fe1.news.blueyonder.co.uk>

Hi Jim,

"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message news:UZ8dd.285025$3l3.150307_at_attbi_s03...
> Even if you are using sqlExecute use bind variables. You should get about
> a
> 10x performance improvement and you will lower the CPU requirements and
> resource usage on the server. You need to close cursors. I think
> sqlExecute does open a cursor.

Yes your right SQLExecute must open a cursor, because I get this problem if I don't free thing properly. It would nice to be able to do things properly and increase the speed. I'm obviously missing a trick here!

> You are probably doing something like
> sqlExecute("insert into mytable(col1,col2,...) values( 1,43,'hi',...)"
> repeat.

Yep, that is exactly what I am doing, although most of the time it is actually Update commands in this Application like: sqlExecute("Update myTable SET myField1=Val1, myField3=Val2... WHERE ID = 2");

Bear in mind I do not know at run time the exact syntax of the update SQL, I take an update command from an INI file that might look like:

Update myTable SET myField1={POM_UID}, myField2={POM_XCOORD}, myField3={POM_YCOORD}

I then replace the token items (i.e. items in {}) and put the WHERE bit on the end of the SQL statement, then pass it to the database.

> That needs bind variables. You are forcing a hard parse for each
> statement
> like that. Unscalable. Also you should use the array interface if you
> are
> inserting multiple rows. Much more efficient.
> Jim

Any chance of some example code (especially using an the array interface)? I would be VERY gratefull! Most of the help on ODBC seems be long on waffle, and short on code examples, especialy MSDN.

Best Regards,

Martin

> "M" <ihatespam.0.a101888_at_spamgourmet.com> wrote in message
> news:rFScd.104894$BI5.24790_at_fe2.news.blueyonder.co.uk...

>> 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 Tue Oct 19 2004 - 12:09:42 CDT