Max Cursor error using Oracle

[sfayed]

Hello,

Does anyone have a good solution to close cursors when using Oracle 9
with VB.NET. I have tried disposing of the command object, the data
adapter and increasing the OPEN_CURSORS variable to a higher number
still nothing consistently works.

When too many cursors are opened, I receive the max cursor error.

Any suggestions would be greatly appreciated!

Thanks,
Scott

We are currently using .NET 1.1 and Oracle 9.

 

[Steve Gerrard]

Hello,

Does anyone have a good solution to close cursors when using Oracle 9
with VB.NET. I have tried disposing of the command object, the data
adapter and increasing the OPEN_CURSORS variable to a higher number
still nothing consistently works.

When too many cursors are opened, I receive the max cursor error.

Any suggestions would be greatly appreciated!

Thanks,
Scott

We are currently using .NET 1.1 and Oracle 9.

I only have more questions:
Are you using the Oracle .Net driver, or the one built into VS?
Are you using connection pooling?
Are you using statement caching?
Are you using wizard generated adapters?
What is OPEN_CURSORS set at?

I am running .Net 2.0 and Oracle 9.2.0.7, and have never encountered this error,
so I know it is possible for it to work right. I can't think of anything in
particular that I do, it just takes care of itself. Maybe if you post more info?

 

[sfayed]

I only have more questions:
  Are you using the Oracle .Net driver, or the one built into VS?
  Are you using connection pooling?
  Are you using statement caching?
  Are you using wizard generated adapters?
  What is OPEN_CURSORS set at?

I am running .Net 2.0 and Oracle 9.2.0.7, and have never encountered this error,
so I know it is possible for it to work right. I can't think of anything in
particular that I do, it just takes care of itself. Maybe if you post moreinfo?- Hide quoted text -

- Show quoted text -

--we are using the microsoft provided Oracle driver
--yes we are using connection pooling
--no, don't think so
--we coded our own data access class
--not sure exactly, I know they doubled the normal value.

 

[Steve Gerrard]

--we are using the microsoft provided Oracle driver
--yes we are using connection pooling
--no, don't think so
--we coded our own data access class
--not sure exactly, I know they doubled the normal value.

I still don't have any great answers, but maybe some more things to look at.

One would be to make a small test app, open a connection, fill one table, close
the connection, then look in Oracle and see if something is up. Connection still
there? Probably, if pooling is on. How many open cursors does it show?

Basically monitoring what is happening should help you find where the trouble
is. Something is causing the cursors to be left open. Are you using Fill on the
adapters, or are you using readers? Are you running lots of different queries,
or are you using parameterized queries to keep the number of different ones
lower?