Help ! Opened cursors exceed max opened cursors

Vincent Leung

unread,

Dec 4, 1997, 3:00:00 AM12/4/97

to

--------------F6E3613D553B51F44375F437
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hi folks,

We use Oracle as Database source to store and maintain our data and
use Microsoft MFC interface to connect Oracle and fetch data. After we
ran our application for about an hour or so, we can not insert or
update our data to the database anymore. We trace the problem using
SQLGetDiagRec which MFC provides. It showed us that opened cursors
exceed max opened cursors. Here is the procedure to retrieve data .

1. get handle using SQLAllocHandle,
2. write a query statement
3. call SQLPrepare to prepare the query statement
4. call SQLExecute to execute query statm
5. Fetch the date using SQLFetch function
6. finally call SQLCloseCursor and SQLFreeStmt with SQL_CLOSE and
SQLFreeStmt with SQL_UNBIND.

We tested the database and saw the table has new inserted data or
updated data there until the cursors error appeared. I understand that
the function SQLFreeStmt with SQL_CLOSE and SQL_UNBIND can help us to
close cursor that has beeen opened after SQLPrepare is called. Have I
misused these database interface which MFC provides?.

We will really appreciate if any one can give us any help on this.
Thanks in advance.

Yan Wang
Phasemetrice, Inc.

--------------F6E3613D553B51F44375F437
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<HTML>
Hi folks,

We use Oracle as Database source to store and maintain
our data and use Microsoft MFC interface to connect Oracle and fetch data.
After we ran our application for about an hour or so,  we can not
insert or update our data to the database anymore.  We  trace
the problem using SQLGetDiagRec which MFC provides.  It showed us
that opened  cursors exceed max opened cursors.  
Here is the procedure to  retrieve data .

1. get handle using SQLAllocHandle,
 2. write a  query statement
 3. call SQLPrepare to prepare the query statement
 4. call SQLExecute to execute query statm
 5. Fetch the date using SQLFetch function
 6. finally call SQLCloseCursor and SQLFreeStmt
with SQL_CLOSE and   SQLFreeStmt with SQL_UNBIND.

We tested the database and saw the table has new inserted data or updated
data there  until the cursors error appeared. I understand that the
function SQLFreeStmt with SQL_CLOSE and SQL_UNBIND can  help us to
close cursor that has beeen opened after SQLPrepare is called. Have I 
misused these database interface which MFC provides?.

We will really appreciate if any one can give us any help on this. Thanks
in advance.

Yan Wang
 Phasemetrice, Inc.
  </HTML>

--------------F6E3613D553B51F44375F437--

Rian Hawkins

unread,

Dec 5, 1997, 3:00:00 AM12/5/97

to

We had the same problem. I added a line in the initorcl.ora file :
OPEN_CURSORS=100.
The default is 50, so the increase took care of our problem.

rhaw...@comptek.nospam.com
Vincent Leung wrote in message <348703EA...@ix.netcomc.com>...

Branko Milosavljevic

unread,

Dec 6, 1997, 3:00:00 AM12/6/97

to

I had a similar problem on MS SQL Server.
I had to do SQLFreeStmt(hstmt, SQL_DROP)
after every SQL statement. That fixed
my problem.

Regards,
Branko Milosavljevic

Joseph Sumalbag

unread,

Dec 12, 1997, 3:00:00 AM12/12/97

to

Hi Vincent,
This is a common problem , And a DBA's common approach is
usually just to increase the size of the initialization
parameter OPEN_CURSORS in the init*.ora file and bounce the
database .

However I do not believe this is the solution to your problem
. You mentioned that your application was running for some time
and then it suddenly stops and gives you the MAX OPEN CURSOR
exceeded message. This only means one thing ,

Inside your loop
you have a procedure that opens a cursor
but you forgot to close it .
So everytime you do the loop a new cursor is opened
untill you reach that point when you hit the
max_cursor allowed for your session.

Adjusting OPEN_CURSORS on this case will only delay the
occurence of the error ....

I like to have OPEN_CURSORS to a low value just to let me detect
if some of the developers are really closing them .

I hope this helps .
Ciao !

Vincent Leung wrote:
>
> Hi folks,
>
> We use Oracle as Database source to store and maintain our data and
> use Microsoft MFC interface to connect Oracle and fetch data. After we
> ran our application for about an hour or so, we can not insert or
> update our data to the database anymore. We trace the problem using
> SQLGetDiagRec which MFC provides. It showed us that opened cursors
> exceed max opened cursors. Here is the procedure to retrieve data .
>
> 1. get handle using SQLAllocHandle,
> 2. write a query statement
> 3. call SQLPrepare to prepare the query statement
> 4. call SQLExecute to execute query statm
> 5. Fetch the date using SQLFetch function
> 6. finally call SQLCloseCursor and SQLFreeStmt with SQL_CLOSE and
> SQLFreeStmt with SQL_UNBIND.
>
> We tested the database and saw the table has new inserted data or
> updated data there until the cursors error appeared. I understand that
> the function SQLFreeStmt with SQL_CLOSE and SQL_UNBIND can help us to
> close cursor that has beeen opened after SQLPrepare is called. Have I
> misused these database interface which MFC provides?.
>
> We will really appreciate if any one can give us any help on this.
> Thanks in advance.
>
> Yan Wang
> Phasemetrice, Inc.
>

> ---------------------------------------------------------------

> Hi folks,
>
> We use Oracle as Database source to store and maintain our data
> and use Microsoft MFC interface to connect Oracle and fetch data.
> After we ran our application for about an hour or so, we can not
> insert or update our data to the database anymore. We trace the
> problem using SQLGetDiagRec which MFC provides. It showed us that
> opened cursors exceed max opened cursors. Here is the procedure to
> retrieve data .
>
> 1. get handle using SQLAllocHandle,
> 2. write a query statement
> 3. call SQLPrepare to prepare the query statement
> 4. call SQLExecute to execute query statm
> 5. Fetch the date using SQLFetch function
> 6. finally call SQLCloseCursor and SQLFreeStmt with SQL_CLOSE and
> SQLFreeStmt with SQL_UNBIND.
>
> We tested the database and saw the table has new inserted data or
> updated data there until the cursors error appeared. I understand
> that the function SQLFreeStmt with SQL_CLOSE and SQL_UNBIND can help
> us to close cursor that has beeen opened after SQLPrepare is called.
> Have I misused these database interface which MFC provides?.
>
> We will really appreciate if any one can give us any help on this.
> Thanks in advance.
>
>
> Yan Wang
> Phasemetrice, Inc.
>

--
================================================================
============================
| Joseph Sumalbag
|
| Oracle DBA
|
|
|
| The opinions expressed above are my own and doesn't
necessarily |
|reflect the opinion of any of my client company or my employer.
|
================================================================
============================