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

"M" <ihatespam.0.a101888_at_spamgourmet.com> wrote in message news:qTbdd.84435$ay5.7530_at_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");

Use bind variables. I am not familiar with the ODBC examples. All the examples I am used to are oci examples that Oracle provides. Did you look at the Application Developer's guide for oracle on otn.oracle.com? Jim

>
> 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 - 20:21:35 CDT