Implict Cursor Closure

[Thargy]

I am currently "doing battle" with a developer who sees no need to close cursors, once used.

I have googled and found the following article

http://www.gennick.com/open_cursors.html

by the illustrious Mr Gennick. He cites conversation with Messrs. Feuerstein and Llewellyn w.r.t. closing cursors, and I would normally consider this definitive.

However, I note that the article dates from 2000. Since then a lot of water has flowed under the bridge, and I wonder if anyone can give me authoritative information about the closure (implicit or otherwise) of

Implicit, Explicit and/or ref cursors.

I'd like to be able to give an informed authoritative response to the rambunctious developer in question.

Regards

T

 

[Thargy]

We have investigated by inspecting v$OPEN_CURSORS and matching SQL_TEXT with the VB recordset definition, and some further googling.

It appears that our developers should be more disciplined in their code, and explicitly close recordsets when they are finished with. They haven't always been doing this, so we are pushing for their coding standards to change to include explicit closure.

By closing a recordset, it does not force Oracle to immediately close the cursor, but allows it to do so. This means that oracle is at liberty to leave cursors available for possible subsequent sharing, and also at liberty to age 'mouldy oldies' out of memory.

At present, the upshot appears to be that provided developers are disciplined with closure statements in their code, open cursors are not a problem for oracle.

Regards

T