Maximum No of Open Cursors Exceeded Error From ORACLE
| Author |
Message |
|
Thalaiappan
#1 / 9
|
 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 |
|
 |
|
Roy Fin
#2 / 9
|
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 |
|
|
|
Wm. G. Urquha
#3 / 9
|
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 |
|
|
|
Roy Fin
#4 / 9
|
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 |
|
|
|
Thalaiappan
#5 / 9
|
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 |
|
|
|
Thalaiappan
#6 / 9
|
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 |
|
|
|
Wm. G. Urquha
#7 / 9
|
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 |
|
|
|
Roy Fin
#8 / 9
|
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 |
|
|
|
Wm. G. Urquha
#9 / 9
|
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 |
|
| |
|
