Skip to main content

Packaged Cursors Equal Global Cursors

Connor McDonald offered a handy reminder of the ability to declare cursors at the package level in his Public / private cursors blog 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.

Generally, 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 demonstrates 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 where in the result set the cursor is pointing) until you explicitly change that state, your session terminates, or the package state is reset.

Is The Cursor Closed?

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 

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

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

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

Block 4
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

OK, hopefully you've got 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?

1. Hide the SQL

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

We can so easily got 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

2. Share the Cursor

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?

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?

Comments

  1. Hi Steven,

    I usually use package cursors -> Always for to share/refactoring.
    I use FOR LOOP - END LOOP -> Always to close.

    I didn't know the use "Hide the SQL" -> Great!

    One note, It happened to me... if your function is recursive and the function use a cursor with recursive call inside.... I can't use un package cursor.... It wil give error:

    {code}
    CREATE OR REPLACE PACKAGE PTEST IS

    FUNCTION funcRecursive(entrance NUMBER) RETURN BOOLEAN;

    END;
    /
    {code}


    {code}
    CREATE OR REPLACE PACKAGE BODY PTEST IS

    CURSOR cTest(entrance NUMBER) IS
    SELECT COL
    FROM
    (SELECT 2 AS COL
    FROM DUAL
    UNION ALL
    SELECT 3 AS COL
    FROM DUAL
    UNION ALL
    SELECT 4 AS COL
    FROM DUAL
    )
    WHERE COL = entrance;

    FUNCTION funcRecursive(entrance NUMBER) RETURN BOOLEAN IS
    newEntrance NUMBER(5);
    BEGIN
    dbms_output.put_line('funcRecursive: ' || entrance);
    IF entrance = 0 THEN
    RETURN FALSE;
    END IF;

    FOR rTest IN cTest(entrance) LOOP
    newEntrance := entrance - 1;
    RETURN funcRecursive(newEntrance);
    END LOOP;


    RETURN TRUE;
    END funcRecursive;

    END;
    /
    {code}

    {code}
    SET SERVEROUTPUT ON
    DECLARE

    BEGIN
    IF PTEST.funcRecursive(4) THEN
    dbms_output.put_line('EXISTS');
    ELSE
    dbms_output.put_line('SORRY... NO');
    END IF;
    END;
    /
    funcRecursive: 4
    funcRecursive: 3
    DECLARE
    *
    ERROR at line 1:
    ORA-06511: PL/SQL: cursor already open
    ORA-06512: at "GEFE.PTEST", line 4
    ORA-06512: at "GEFE.PTEST", line 25
    ORA-06512: at "GEFE.PTEST", line 27
    ORA-06512: at line 4
    {code}


    Then, change:

    {code}
    CREATE OR REPLACE PACKAGE BODY PTEST IS


    FUNCTION funcRecursive(entrance NUMBER) RETURN BOOLEAN IS
    newEntrance NUMBER(5);

    CURSOR cTest(entrance NUMBER) IS
    SELECT COL
    FROM
    (SELECT 2 AS COL
    FROM DUAL
    UNION ALL
    SELECT 3 AS COL
    FROM DUAL
    UNION ALL
    SELECT 4 AS COL
    FROM DUAL
    )
    WHERE COL = entrance;

    BEGIN
    dbms_output.put_line('funcRecursive: ' || entrance);
    IF entrance = 0 THEN
    RETURN FALSE;
    END IF;

    FOR rTest IN cTest(entrance) LOOP
    newEntrance := entrance - 1;
    RETURN funcRecursive(newEntrance);
    END LOOP;


    RETURN TRUE;
    END funcRecursive;

    END;
    /
    {code}


    {code}
    SET SERVEROUTPUT ON
    DECLARE

    BEGIN
    IF PTEST.funcRecursive(4) THEN
    dbms_output.put_line('EXISTS');
    ELSE
    dbms_output.put_line('SORRY... NO');
    END IF;
    END;
    /
    funcRecursive: 4
    funcRecursive: 3
    funcRecursive: 2
    funcRecursive: 1
    EXISTS

    PL/SQL procedure successfully completed.
    {code}

    Thank you.

    ReplyDelete
    Replies
    1. Thanks for raising this point about package-level cursors and a recursive function + all that code to prove your point!

      Delete
  2. Hello All,
    Just a short comment regarding the code above:

    You have the following loop:

    FOR rTest IN cTest(entrance) LOOP
    newEntrance := entrance - 1;
    RETURN funcRecursive(newEntrance);
    END LOOP;

    I see several problems with writing such code:

    1. The loop does NOT use the rTest iterator record at all.
    2. A RETURN statement is executed already as part of the first iteration of the loop,
    and, since the cursor is a package level cursor, it remains open.

    I think that using a FOR loop for executing one iteration only is better to be avoided.

    Anyway, even if executing a single iteration, placing the RETURN statement AFTER
    the END LOOP is a cleaner coding style, and, as an additional result, the cursor will be closed.

    Thanks a lot & Best Regards,
    Iudith



    ReplyDelete

Post a Comment

Popular posts from this blog

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.

How to Get a Mutating Table Error

I need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

Table Functions, Part 1: Introduction and Exploration

August 2018 update: please do feel encourage to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!


Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latte…

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post.

PL/SQL is a strongly-typed language. Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type).

PL/SQL offers a wide array of pre-defined data types, both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package).

Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types.

You can't really declare your own "user-defined" scalars, though you can define subtypes from those scalars, which can be very helpful from the perspective…