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

"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 Sun Oct 17 2004 - 14:18:52 CDT