Edwinah63 wrote:
> i have an old VB6 application that has recently had the drivers changed
> from Microsoft provider for Oracle (MSDAORA) to Oracle provider for OLE
> DB (OraOLEDB.Oracle) for CLOB support on certain tables.
>
> unfortunately, we now get the "ORA-01000 maximum open cursors
> exceeded", a problem we never had with the old driver.
>
> checking the number of open cursors for a given session shows that they
> are accumulating instead of being extinguished!!!
>
> eg:
>
> rs.open "select * from myTable"
>
> //do something with recordset
>
> rs.close 'Nothing happens!!!! cursor remains open on server :(
>
> have googled around and found the most common solution is to simply
> close the connection, but in this apps case, the connection is OPENED
> ONCE and kept alive for the duration of the life of the session.
>
> what we have:
>
> * windows 2000
> * VB 6
> * ADO 2.5
> * Oracle ole db provider 8.17
> * database set to dedicated connections, not shared pool.
> * Oracle 9.2 patch set 5
>
> what would like to do:
>
> * would prefer not to close connections after each transaction due to
> large-ish code rewrite
> * if possible would like to programatically tell Oracle to close all
> open cursors for the given session. code sample please
> * would newer drivers fix this problem?
>
> any help greatly appreciated
>
> Edwinah63
>
Try %isopen for the (named) cursor - no need to open
an open cursor. Same technique applies to closing:
if [cursor_name]%ISOPEN then close [cursor_name];
--
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
Received on Wed Nov 30 2005 - 05:58:32 CST
