Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Lifetime of a cursor in Oracle

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?

like image 400
rhkvik Avatar asked Jun 07 '10 17:06

rhkvik


People also ask

How many records can a cursor hold in Oracle?

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.

How many rows a cursor can hold?

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.

How many cursors can be opened at a time?

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.

Which cursor is faster in Oracle?

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.


2 Answers

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);

like image 70
Gary Myers Avatar answered Oct 14 '22 10:10

Gary Myers


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.

like image 31
DCookie Avatar answered Oct 14 '22 11:10

DCookie