Maximum No of Open Cursors Exceeded Error From ORACLE 
Author Message
 Maximum No of Open Cursors Exceeded Error From ORACLE

Hiii to all,
    I am using ADO 2.6 to connect to ORACLE 8i database From VC++.
my odbc driver is Microsoft ODBC driver.
i am using connection and recordset objects to connect to my database.
once my operation is over, i am closing my recordset.  But after some
10 database operation i am getting error message " Maximum Number of
Open Cursors Exceeded " from the Oracle database.  Now i have two
questions.

1. How to control the "cursors" of ORACLE database throught ADO..??
2. Is there any way by which we can increase the maximum number of
    open cursors in ORACLE database.

This is the code which i am using. I am createing connection object once,
and recordset object many times where ever i want to make new query.

/*****************************************************
 varDataSource  = csDSN;
 varUserId   = csUser;
 varPwd    = csDecPassword;
 CLSID clsid;
 USES_CONVERSION;
 hr = CLSIDFromProgID(A2OLE("ADODB.Connection"), &clsid);
 try
 {

 THROW_ERR(   CoInitialize(NULL));
  THROW_ERR(   CoCreateInstance(clsid, NULL, CLSCTX_INPROC_SERVER  ,
IID_IUnknown, (LPVOID *)&m_piConnection));
  THROW_ERR(   m_piConnection->Open( varDataSource, varUserId, varPwd,
adOpenUnspecified ));
  hr = CLSIDFromProgID(A2OLE("ADODB.Recordset"), &clsid);
  THROW_ERR(   CoCreateInstance(clsid, NULL, CLSCTX_INPROC_SERVER  ,
IID_IUnknown, (LPVOID *)&m_piEmpRecordSet));
  THROW_ERR(   m_piEmpRecordSet->putref_ActiveConnection(m_piConnection));

  CString csSQL = "SELECT VALIDCONFIG FROM ADM_ADMIN_FLAG ";
  bstrSQL = csSQL.AllocSysString();
  THROW_ERR(   m_piEmpRecordSet->put_Source(bstrSQL));
  THROW_ERR(   m_piEmpRecordSet->Open(vNull, vNull, adOpenKeyset,
adLockOptimistic, adCmdText));

  THROW_ERR(  m_piEmpRecordSet->get_EOF(&vbEOF));
  THROW_ERR(  m_piEmpRecordSet->get_BOF(&vbBOF));

  if(vbEOF && vbBOF)
   m_fRecordsetEmpty  = TRUE;
  else
   m_fRecordsetEmpty  = FALSE;

  if(m_fRecordsetEmpty)
  {
   SetErrorMessage("ConfigFlag is Not Available in the Database");
   return EMAKEHR(0X0010);
  }
   THROW_ERR(  m_piEmpRecordSet->get_Collect(vValConfigFlag ,  vFldData));
   nvarConfig->intVal = vFldData.intVal ;

  m_piEmpRecordSet->Close();

Quote:
}

 catch (HRESULT hr)
 {
  // call the system error messge function to display error, in case of
error
  SetSystemErrorMessage(hr);
  return MAKE_HRESULT(SEVERITY_ERROR, FACILITY_ITF, 0X0010);
 }
*****************************************************/


Sun, 18 Apr 2004 13:35:57 GMT
 Maximum No of Open Cursors Exceeded Error From ORACLE

Thalaiappan S

After you close the recordset, you should close the connection as well.
Then, after bother db objects are closed, you MUST do a release on the TWO
interfaces.

Additionally, your handling of the BOF condition will bypass the object
closing and the interfaces releases.  This needs to be fixed as well.

regards
Roy Fine


Quote:
> Hiii to all,
>     I am using ADO 2.6 to connect to ORACLE 8i database From VC++.
> my odbc driver is Microsoft ODBC driver.
> i am using connection and recordset objects to connect to my database.
> once my operation is over, i am closing my recordset.  But after some
> 10 database operation i am getting error message " Maximum Number of
> Open Cursors Exceeded " from the Oracle database.  Now i have two
> questions.

> 1. How to control the "cursors" of ORACLE database throught ADO..??
> 2. Is there any way by which we can increase the maximum number of
>     open cursors in ORACLE database.

> This is the code which i am using. I am createing connection object once,
> and recordset object many times where ever i want to make new query.

/* **************************************************** */
 varDataSource  = csDSN;
 varUserId   = csUser;
 varPwd    = csDecPassword;
 CLSID clsid;
 USES_CONVERSION;
 hr = CLSIDFromProgID(A2OLE("ADODB.Connection"), &clsid);
 try {

  CoInitialize(NULL);
  CoCreateInstance(clsid, NULL, CLSCTX_INPROC_SERVER  ,IID_IUnknown, (LPVOID
*)&m_piConnection));
  m_piConnection->Open( varDataSource, varUserId, varPwd,
adOpenUnspecified );
  hr = CLSIDFromProgID(A2OLE("ADODB.Recordset"), &clsid);
  CoCreateInstance(clsid, NULL, CLSCTX_INPROC_SERVER  , IID_IUnknown,
(LPVOID *)&m_piEmpRecordSet);
  m_piEmpRecordSet->putref_ActiveConnection(m_piConnection);

  CString csSQL = "SELECT VALIDCONFIG FROM ADM_ADMIN_FLAG ";
  bstrSQL = csSQL.AllocSysString();
  m_piEmpRecordSet->put_Source(bstrSQL);
  m_piEmpRecordSet->Open(vNull, vNull, adOpenKeyset,adLockOptimistic,
adCmdText);

  m_piEmpRecordSet->get_EOF(&vbEOF);
  m_piEmpRecordSet->get_BOF(&vbBOF);

  if(vbEOF && vbBOF)  m_fRecordsetEmpty  = TRUE;
  else  m_fRecordsetEmpty  = FALSE;

  if(m_fRecordsetEmpty)  {
    SetErrorMessage("ConfigFlag is Not Available in the Database");
    return EMAKEHR(0X0010);
    }
 m_piEmpRecordSet->get_Collect(vValConfigFlag ,  vFldData);
 nvarConfig->intVal = vFldData.intVal ;

  m_piEmpRecordSet->Close();
  }
catch (HRESULT hr) {
  SetSystemErrorMessage(hr);
  return MAKE_HRESULT(SEVERITY_ERROR, FACILITY_ITF, 0X0010);
  }



Mon, 19 Apr 2004 04:14:28 GMT
 Maximum No of Open Cursors Exceeded Error From ORACLE

Quote:
> Thalaiappan S

> After you close the recordset, you should close the connection as well.
> Then, after bother db objects are closed, you MUST do a release on the TWO
> interfaces.

> Additionally, your handling of the BOF condition will bypass the object
> closing and the interfaces releases.  This needs to be fixed as well.

> regards
> Roy Fine



> > Hiii to all,
> >     I am using ADO 2.6 to connect to ORACLE 8i database From VC++.
> > my odbc driver is Microsoft ODBC driver.
> > i am using connection and recordset objects to connect to my database.
> > once my operation is over, i am closing my recordset.  But after some
> > 10 database operation i am getting error message " Maximum Number of
> > Open Cursors Exceeded " from the Oracle database.  Now i have two
> > questions.

> > 1. How to control the "cursors" of ORACLE database throught ADO..??
> > 2. Is there any way by which we can increase the maximum number of
> >     open cursors in ORACLE database.

> > This is the code which i am using. I am createing connection object once,
> > and recordset object many times where ever i want to make new query.

> /* **************************************************** */
>  varDataSource  = csDSN;
>  varUserId   = csUser;
>  varPwd    = csDecPassword;
>  CLSID clsid;
>  USES_CONVERSION;
>  hr = CLSIDFromProgID(A2OLE("ADODB.Connection"), &clsid);
>  try {

>   CoInitialize(NULL);
>   CoCreateInstance(clsid, NULL, CLSCTX_INPROC_SERVER  ,IID_IUnknown, (LPVOID
> *)&m_piConnection));
>   m_piConnection->Open( varDataSource, varUserId, varPwd,
> adOpenUnspecified );
>   hr = CLSIDFromProgID(A2OLE("ADODB.Recordset"), &clsid);
>   CoCreateInstance(clsid, NULL, CLSCTX_INPROC_SERVER  , IID_IUnknown,
> (LPVOID *)&m_piEmpRecordSet);
>   m_piEmpRecordSet->putref_ActiveConnection(m_piConnection);

>   CString csSQL = "SELECT VALIDCONFIG FROM ADM_ADMIN_FLAG ";

Hi,

To increase the number of open cursors in Oracle edit the instance's
init.ora file and add or change the line OPEN_CURSORS = nn. Where nn is
the number of cursors you want.

As regards the ADO see above.

--
Kind regards,

-William



Mon, 19 Apr 2004 20:36:30 GMT
 Maximum No of Open Cursors Exceeded Error From ORACLE
but in an application that would have but one cusrsor open at a time, and
when that app runs for a length of time and runs out of cursors, one would
think that increasing the limit would only serve to lengthen the time before
the cursor limit is reached -- better to attack the root cause first...

regards,

roy fine



Quote:

> > Thalaiappan S

> > After you close the recordset, you should close the connection as well.
> > Then, after bother db objects are closed, you MUST do a release on the
TWO
> > interfaces.

> > Additionally, your handling of the BOF condition will bypass the object
> > closing and the interfaces releases.  This needs to be fixed as well.

> > regards
> > Roy Fine



> > > Hiii to all,
> > >     I am using ADO 2.6 to connect to ORACLE 8i database From VC++.
> > > my odbc driver is Microsoft ODBC driver.
> > > i am using connection and recordset objects to connect to my database.
> > > once my operation is over, i am closing my recordset.  But after some
> > > 10 database operation i am getting error message " Maximum Number of
> > > Open Cursors Exceeded " from the Oracle database.  Now i have two
> > > questions.

> > > 1. How to control the "cursors" of ORACLE database throught ADO..??
> > > 2. Is there any way by which we can increase the maximum number of
> > >     open cursors in ORACLE database.

> > > This is the code which i am using. I am createing connection object
once,
> > > and recordset object many times where ever i want to make new query.

> > /* **************************************************** */
> >  varDataSource  = csDSN;
> >  varUserId   = csUser;
> >  varPwd    = csDecPassword;
> >  CLSID clsid;
> >  USES_CONVERSION;
> >  hr = CLSIDFromProgID(A2OLE("ADODB.Connection"), &clsid);
> >  try {

> >   CoInitialize(NULL);
> >   CoCreateInstance(clsid, NULL, CLSCTX_INPROC_SERVER  ,IID_IUnknown,
(LPVOID
> > *)&m_piConnection));
> >   m_piConnection->Open( varDataSource, varUserId, varPwd,
> > adOpenUnspecified );
> >   hr = CLSIDFromProgID(A2OLE("ADODB.Recordset"), &clsid);
> >   CoCreateInstance(clsid, NULL, CLSCTX_INPROC_SERVER  , IID_IUnknown,
> > (LPVOID *)&m_piEmpRecordSet);
> >   m_piEmpRecordSet->putref_ActiveConnection(m_piConnection);

> >   CString csSQL = "SELECT VALIDCONFIG FROM ADM_ADMIN_FLAG ";
> Hi,

> To increase the number of open cursors in Oracle edit the instance's
> init.ora file and add or change the line OPEN_CURSORS = nn. Where nn is
> the number of cursors you want.

> As regards the ADO see above.

> --
> Kind regards,

> -William



Tue, 20 Apr 2004 09:01:02 GMT
 Maximum No of Open Cursors Exceeded Error From ORACLE
Hii roy,
     thanks for your suggestion. Actually in my dll, i am making only one
connection object,
and i am using that connection for all the recordset objects. If i close and
open
connection for each and every recordset operation, will not be too much
overhead
for processing. meaning, every time connection needs to esatablish , rather
i can
use the already opened connection know.  And in my applicatin as of now that
BOF condition will not at all come. So even after correcting that BOF i am
getting
the same error . By any way can we control the opened Cursor in Database
using
ADO Api's..??

regards,
Thals.



Tue, 20 Apr 2004 14:45:38 GMT
 Maximum No of Open Cursors Exceeded Error From ORACLE
Hii roy,
     thanks for your suggestion. Actually in my dll, i am making only one
connection object,
and i am using that connection for all the recordset objects. If i close and
open
connection for each and every recordset operation, will not be too much
overhead
for processing. meaning, every time connection needs to esatablish , rather
i can
use the already opened connection know.  And in my applicatin as of now that
BOF condition will not at all come. So even after correcting that BOF i am
getting
the same error . By any way can we control the opened Cursor in Database
using
ADO Api's..??

regards,
Thals.



Tue, 20 Apr 2004 14:46:09 GMT
 Maximum No of Open Cursors Exceeded Error From ORACLE

<snip>

Hi Guys,

From what I can make of your code all you have a single connection to
the database, this is not a cursor.

Each time you execute a SQL statement against the DB using a RecordSet
for example the Driver Manager allocates a cursor for it. So if you
neglect to close these RecordSets then the cursor will remain valid.

HTH

--
Kind regards,

Wm. G. Urquhart



Wed, 21 Apr 2004 19:05:20 GMT
 Maximum No of Open Cursors Exceeded Error From ORACLE



Quote:

> Hi Guys,

> From what I can make of your code all you have a single connection to
> the database, this is not a cursor.

> Each time you execute a SQL statement against the DB using a RecordSet
> for example the Driver Manager allocates a cursor for it. So if you
> neglect to close these RecordSets then the cursor will remain valid.
> HTH
> Kind regards,
> Wm. G. Urquhart

William,
Consider the following snip from the OP:

Quote:
> hr = CLSIDFromProgID(A2OLE("ADODB.Recordset"), &clsid);
>  THROW_ERR(   CoCreateInstance(clsid, NULL, CLSCTX_INPROC_SERVER  ,
...
>  THROW_ERR(   m_piEmpRecordSet->put_Source(bstrSQL));
>  THROW_ERR(   m_piEmpRecordSet->Open(vNull, vNull, adOpenKeyset,

there is more than just a db connection -- each of the calls to Open method
on the Recordset object will create a cursor.

roy fine



Wed, 21 Apr 2004 23:54:11 GMT
 Maximum No of Open Cursors Exceeded Error From ORACLE

Quote:




> > Hi Guys,

> > From what I can make of your code all you have a single connection to
> > the database, this is not a cursor.

> > Each time you execute a SQL statement against the DB using a RecordSet
> > for example the Driver Manager allocates a cursor for it. So if you
> > neglect to close these RecordSets then the cursor will remain valid.
> > HTH
> > Kind regards,
> > Wm. G. Urquhart

> William,
> Consider the following snip from the OP:

> > hr = CLSIDFromProgID(A2OLE("ADODB.Recordset"), &clsid);
> >  THROW_ERR(   CoCreateInstance(clsid, NULL, CLSCTX_INPROC_SERVER  ,
> ...
> >  THROW_ERR(   m_piEmpRecordSet->put_Source(bstrSQL));
> >  THROW_ERR(   m_piEmpRecordSet->Open(vNull, vNull, adOpenKeyset,

> there is more than just a db connection -- each of the calls to Open method
> on the Recordset object will create a cursor.

> roy fine

Well that's what I get for not paying attention to what's in front of
me. Having said that what I said with the above exception is still
valid.

--
Kind regards,

Wm. G. Urquhart



Thu, 22 Apr 2004 00:13:44 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. Oracle/JDBC Error - maximum open cursors exceeded

2. Error ORA-01000: Maximum Open Cursors Exceeded in CLOSE_THIS_WINDOW referenced trigger

3. ERROR - ORA-0100-Maximum open cursors exceeded

4. ORA-01000 :Maximum Open Cursors Exceeded Error ??

5. JDBC Error:maximum open cursors exceeded !

6. ORA-01000 :Maximum Open Cursors Exceeded Error ??

7. ORA-01000 maximum open cursors exceeded - Delphi - Oracle

8. Oracle: maximum open cursors exceeded...

9. ORACLE/D3 Maximum open cursors exceeded problem

10. Java with Oracle AQ - maximum open cursors exceeded

11. Oracle/JDBC: maximum number of open cursors exceeded

12. maximum open cursors exceeded


 
Powered by phpBB® Forum Software