Packaged Cursors = Global Cursors

Connor McDonald offered a handy reminder of the ability to declare cursors at the package level in his Public/private cursors post.

I offer this follow-on to make sure you are aware of the use cases for doing this and also the different behavior you will see for package-level cursors.

First of all, let's remind ourselves of the difference between items declared at the package level ("global") and those declared within subprograms of the package ("local").

Items declared locally are automatically cleaned up ("closed" and memory released) when the block terminates.

Items declared globally are kept "open" (memory allocated, state preserved) until the session terminates.

Here's are two LiveSQL scripts that demonstrate the difference for a numeric and string variable (not that the types matter).

Global vs. Local variable (community contribution)
Global (Package-level) vs. Local (Declared in block) Variables (mine)

But this principle also applies to cursors, and the ramifications can be a bit more critical to your application. Let's explore in more detail.

From Understanding the PL/SQL Package Specification:

The scope of a public item is the schema of the package. A public item is visible everywhere in the schema.

and when it comes to the package body:

The package body can also declare and define private items that cannot be referenced from outside the package, but are necessary for the internal workings of the package.

The "scope" describes who/where in your code the packaged element can be referenced. Roughly, any schema or program unit with the execute privilege granted to the package can reference any element in the package specification.

But in this case, we are more interested in what you "see" when you reference that packaged item: the state of that item.

Global items maintain their state (for a variable, its value; for a cursor, as you are about to see, its status of open or closed and if open wherein the result set the cursor is pointing) until you explicitly change that state, your session terminates, or the package state is reset.

Let's watch this in action, via a little quiz for you drawn from the Oracle Dev Gym. Suppose I execute the following statements below (try it in LiveSQL):

CREATE TABLE employees
(
   employee_id   INTEGER,
   last_name     VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO employees VALUES (100, 'Thomsoni');
   INSERT INTO employeesVALUES (200, 'Edulis');
   COMMIT;
END;
/

CREATE OR REPLACE PACKAGE pkg
IS
   CURSOR emp_cur (id_in IN employees.employee_id%TYPE)
   IS
      SELECT last_name
        FROM employees
       WHERE employee_id = id_in;
END;
/

CREATE OR REPLACE PROCEDURE show_name1 (
   id_in   IN employees.employee_id%TYPE)
IS
   l_name   employees.last_name%TYPE;
BEGIN
   OPEN pkg.emp_cur (id_in);
   FETCH pkg.emp_cur INTO l_name;
   DBMS_OUTPUT.put_line (l_name);
END;
/

CREATE OR REPLACE PROCEDURE show_name2 (
   id_in   IN employees.employee_id%TYPE)
IS
   CURSOR emp_cur (id_in IN employees.employee_id%TYPE)
   IS
      SELECT last_name
        FROM employees
       WHERE employee_id = id_in;

   l_name   employees.last_name%TYPE;
BEGIN
   OPEN emp_cur (id_in);
   FETCH emp_cur INTO l_name;
   DBMS_OUTPUT.put_line (l_name);
END;
/

Which of the blocks below will display the following two lines of text after execution?

Thomsoni 
Edulis 

BEGIN
   show_name1 (100);
   show_name1 (200);
END;

BEGIN
   show_name2 (100);
   show_name2 (200);
END;

BEGIN
   show_name1 (100);
   CLOSE pkg.emp_cur;
   show_name1 (200);
   CLOSE pkg.emp_cur;
END;

BEGIN
   show_name1 (100);
   show_name2 (200);
END;

And the answer is: blocks 2 - 4 will display the desired output, while block 1 fails with:

ORA-06511: PL/SQL: cursor already open 

Block 2 works just fine because show_name2 uses a locally-declared cursor. The cursor is opened locally and when the procedure terminates, the cursor is closed.

But in block 1, I am calling show_name1, which opens the package-based cursor. And since the cursor is declared at the package level, once it is opened, it stays open in your session. Even when the procedure in which it was opened terminates.

If you do, however, explicitly close the cursor, then you are able to open it again in that same session, which is why block 3 succeeds.

Block 4 shows the desired output as well because the packaged cursor is only opened by the first procedure call. The second uses the local "copy" and so there is no error. If, however, you tried to call show_name1 again in the same session, ORA-06511 will be raised since the packaged cursor was opened, but not closed, inside the procedure. It remains open when the plch_show_name1 procedure is called a second time.

Use Cases for Package-Level Cursors

Okay, hopefully you've gotten a handle on the different behavior of cursors defined at the package level. Why would you want to define a cursor this way as opposed to just "burying" the cursor/SQL inside a particular block?

Hide the SQL

Hiding information is often a very good idea when it comes to software development.

We can so easily get lost in the "weeds" — the details of how things are implemented — rather than stay at a higher level that focuses on how to use the element in question. That's the whole idea behind APIs.

How does that work with cursors? You can declare the cursor header in the package specification and move the SELECT to the package body! As in:

CREATE OR REPLACE PACKAGE pkg
IS
   CURSOR emp_cur (id_in IN employees.employee_id%TYPE)
      RETURN employees%ROWTYPE;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg
IS
   CURSOR emp_cur (id_in IN employees.employee_id%TYPE)
      RETURN employees%ROWTYPE
   IS
      SELECT *
        FROM employees
       WHERE employee_id = id_in;
END;
/

The type of the RETURN statement must be a record type. If you try to return say a scalar value type, as in "employees.last_name%TYPE, you will get this compilation error:

PLS-00320: the declaration of the type of this expression is incomplete or malformed 

The main driver for going to package-level cursors is that the cursor is not embedded within a single procedure, function or anonymous block. Which means that you can reference (open-fetch-close) that cursor from multiple subprograms and blocks.

That's nice — you avoid repetition of the same cursor.

That's potentially an issue because you need to make sure the cursor is not open already before you try to open it. And if it is open, what should you do?

The bottom line for this use case: each user of the cursor must be sure to close the cursor when they are done.

Have you declared cursors at the package level? Any interesting stories you'd like to share with us?