Re: Help ! Opened cursors exceed max opened cursors

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.                            
|
================================================================
============================