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?
there is no limit (i'm aware of) to the number of rows a cursor can process. Obviously, if you try and process 50 million records from a cursor, your program won't likely finish anytime soon.
A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed.
The OPEN_CURSORS parameter sets the maximum number of cursors that each session can have open, per session. For example, if the value of OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time.
The short answer is that implicit cursors are faster and result in much neater code so there are very few cases where you need to resort to explicit cursors.
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);
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With