Can i explicitly close all open cursors for a given SID?

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 Received on Tue Nov 29 2005 - 23:04:29 CST