Lifetime of a cursor in Oracle

Question

If I open a cursor and forget to close it, for how long will the cursor exist in Oracle?

Are there some settings to configure the lifetime of the cursor?

Answer 1

I believe it lives until your session goes away or closes it.

You can configure the lifetime of the cursor by closing it when you're done with it ;-)

Seriously, relying on some setting to close your cursors for you just hides problems and promotes lazy programming. It could also have a detrimental effect on any process that legitimately needs to keep a cursor open a longer period of time.

Answer 2

As well as DCookie's answer:

The general pattern of a cursor lifetime is

OPEN 
BIND
EXECUTE
FETCH...FETCH...FETCH
BIND
EXECUTE
FETCH...FETCH...FETCH
...
CLOSE

That is a cursor can be re-used by binding fresh variables to it and re-executing it.

PL/SQL will also maintain a cache of cursors, at the session level, to avoid the overhead of re-opening a cursor you have recently closed. So a cursor you have programmatically closed may still be open. Oracle will close them behind the scenes when appropriate.

If a cursor goes out of scope, it can be closed. That is if a call or procedure is like:

DECLARE
  CURSOR c_1 IS SELECT ....;
BEGIN
  OPEN c_1;
  FETCH c_1 INTO...;
END;

then once it has finished executing, c_1 goes out of scope (and cannot physically be called again) and can be closed. This is especially useful for exception handling, since it is possible for an exception to be raised, jumping out of the procedure and bypassing all your 'CLOSE c_1;' code. By closing the out-of-scope cursors, you don't have to worry about adding exception handling code to do that.

If your cursor has a session scope (eg defined in a PL/SQL package specification or global level of a body or returned to a client through a ref cursor) it never goes out of scope in this way, so would never be automatically closed until the session disconnects or through a DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.FREE_ALL_RESOURCES);