More on PL/SQL's Cursor Handling

 

If you've somehow jumped directly to this page, perhaps from a search engine, you should read the following articles as background for understanding this one: Does PL/SQL Implicitly Close Cursors?
I also participated in a long discussion with Steven Feuerstein, Bill Pribyl, and Bryn Llewellyn (the PL/SQL product manager). Read about that in the conclusion to this whole saga: PL/SQL Cursor Handling Explained.
Finally, you might find my article On the Importance of Mental Models to be interesting. It essentially explains why I feel it's important to know what's going on "under the hood."

 

A few days ago, I wrote Does PL/SQL Implicitly Close Cursors in response to reader question about whether PL/SQL would implicitly close any open cursors at the end of a block. In that article, I show fairly conclusive evidence that PL/SQL does not implicitly close cursors when a block ends and the cursor variables go out of scope. At the same time, I reported on evidence that somehow any open cursors do get closed when the outermost block exits and control returns back to SQL*Plus. My initial reaction to what I found in my research was that ideally PL/SQL should close any open cursors at the end of a block. However, that same reader recently sent me a snippet of code that has caused me to rethink my position. There's more to this issue than meets the eye.

The Reader's Code

The code the reader sent me is the following mixture of SQL*Plus and PL/SQL:

set serveroutput on
variable g_ref refcursor

DECLARE
 TYPE emp_cur_t is REF CURSOR return emp%rowtype;  
 v_cur      emp_cur_t;
 emp_rec     emp%rowtype;
BEGIN
 OPEN v_cur for select * from emp where rownum <=6; 
 FOR idx in 1..3 LOOP  
   FETCH v_cur into emp_rec;
   DBMS_OUTPUT.PUT_LINE(emp_rec.ename);
 END LOOP;
 :g_ref := v_cur;  
END;
/

print g_ref 

Connect to the sample SCOTT schema, paste the above code into SQL*Plus, and the following sequence of events occurs:

  1. The variable command creates a SQL*Plus refcursor variable

  2. The PL/SQL block opens a cursor (named v_cur) on the emp table

  3. The PL/SQL block fetches the first three rows and displays the employee names from those rows

  4. The PL/SQL block assigns v_cur to the SQL*Plus refcursor variable

  5. The PL/SQL block exits, leaving the cursor unclosed

  6. The SQL*Plus print command causes the remaining data to be fetched and displayed

That the above sequence of events happens is conclusive proof that PL/SQL does not implicitly close cursors when they go out of scope. It also raises some issues and questions in my mind, but before I get to those let's look at how I might have written the same code.

The Approach I Might Have Used

The code the reader sent me reminded me of an example that I used in my book, Oracle SQL*Plus: The Definitive Guide. (See Chapter 7, Page 254, the section titled "Using REFCURSOR Variables to Simulate Branching") It bothered me at first that the reader created a cursor variable within PL/SQL, opened that cursor, and then assigned the cursor to a cursor variable of higher scope. The following approach operates directly on the SQL*Plus refcursor:

set serveroutput on
variable g_ref refcursor

DECLARE

 emp_rec     emp%rowtype;
BEGIN
 OPEN :g_ref for select * from emp where rownum <=6; 
 FOR idx in 1..3 LOOP  
   FETCH :g_ref into emp_rec;
   DBMS_OUTPUT.PUT_LINE(emp_rec.ename);
 END LOOP; 
END;
/

print g_ref 

When I wrote this code, I initially was pleased that I was no longer using a cursor outside the scope of the block in which I declared it. Now, I'm not so sure that even matters.

Issues and Questions

These two code examples raise some interesting philosophical issues:

  • Consider the first example, in which the cursor was declared and opened from within the PL/SQL block. When the block exits and the cursor variable goes out of scope, what should be the disposition of the cursor itself? Should the cursor be implicitly closed?

  • If you want a cursor to be available outside the PL/SQL block in which it is opened, should you have to declare that variable outside the block to begin with, as is done in the second example?

  • If you open a cursor within a block, but it's declared outside of that block, should PL/SQL implicitly close that cursor when the inner block (the opening block) ends? Or do you only implicitly close the cursor when the cursor variable goes out of scope? Or do you never implicitly close the cursor at all?

Implicitly closing the v_cur cursor at the end of the block when it went out of scope would "break" the code that the reader sent me. It would also break any packaged functions that happen to return REFCURSOR values. I know there are some out there, because I've written some! In fact, I think there are one or two Oracle-supplied-package functions that return REFCURSOR values. I don't think Oracle is about to change current behavior, nor am I entirely convinced that they should. Perhaps what's needed is to have a correct "mental model" when working with cursors.

A Mental Model for Cursors

Mental models of how things work are important, and I'll write more about that issue in a future article. For now, I'll just describe the mental model that I'm currently forming related to this cursor issue. Key to that model are the following assertions:

  • A PL/SQL cursor variable is a pointer (analagous to a pointer in C or C++).

  • cursor is distinct from a cursor variable, and represents a structure in memory.

  • When you declare a cursor, you get a pointer variable that doesn't point to anything.

  • When you open a cursor, memory is allocated and the cursor's structure is created. The cursor variable, a pointer, now points to the cursor itself.

  • When you close a cursor, the memory being used for the cursor's structure is released.

I'm still left with a few questions, which I'll detail in another article, but this model explains most of the behavior that I see. When you think of a cursor as a structure that exists independently of any cursor variable, it makes perfect sense that a cursor remains open even after the associated cursor variable goes out of scope. In the first code example earlier, it's the pointer that is being passed back to SQL*Plus, not the cursor itself. When SQL*Plus closes the cursor (the PRINT command will close the cursor after the printing occurs), the cursor's memory is released.