Understanding PRAGMA SERIALLY_REUSABLE In Oracle 19c

reference_partition

In Oracle, Package data consists of variables and constants that are defined at the package level—that is, not within a particular function or procedure in the package. The scope of the package data is therefore not a single program, but rather the package as a whole. In the PL/SQL runtime architecture, package data structures persist for the duration of a session (rather than the duration of execution for a particular program).

  • If package data is declared inside the package body, then that data persists for the session but can be accessed only by elements defined in the package itself (private data).
  • If package data is declared inside the package specification, then that data persists for the session and is directly accessible (to both read and modify the value) by any program that has EXECUTE authority on that package (public data).

Package data by default persists for your entire session (or until the package is recompiled or you change the value explicitly).

This is an incredibly handy feature, but it has some drawbacks :-

  • Globally accessible (public and private) data structures persist, and that can cause undesired side effects like leaving a packaged cursors open, causing “already open” errors in other programs.

ORA-06511: PL/SQL: cursor already open

  • Programs can suck up lots of real memory (package data is managed in the user’s memory area or user global area [UGA]) and then not release it if that data is stored in a package-level structure.

To help you manage the use of memory in packages, PL/SQL offers the SERIALLY_REUSABLE pragma.

This pragma, which must appear in both the package specification and the body marks that package as serially reusable.

For such packages, the duration of package state (the values of variables, the open status of a packaged cursor, etc.) can be reduced from a whole session to a single call of a program in the package.

The SERIALLY_REUSABLE pragma indicates that the package state is needed only for the duration of one call to the server (for example, an OCI call to the database or a stored procedure call through a database link).

--Without Pragma SERIALLY_REUSABLE
SQL> CREATE OR REPLACE PACKAGE test_pkg
  2  IS
  3    CURSOR emp_cur IS SELECT * FROM emp;
  4
  5    PROCEDURE open_cursor;
  6
  7    PROCEDURE fetch_cursor;
  8
  9    PROCEDURE close_cursor;
 10
 11    var1 NUMBER := 1;
 12    var2 NUMBER := 2;
 13  END test_pkg;
 14  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY test_pkg
  2  IS
  3     var3 NUMBER := 3;
  4     var4 NUMBER := 4;
  5
  6     PROCEDURE open_cursor
  7     IS
  8       BEGIN
  9          OPEN emp_cur;
 10      END;
 11
 12      PROCEDURE fetch_cursor
 13      IS
 14       emp_rec emp%ROWTYPE;
 15        BEGIN
 16            LOOP
 17                 FETCH emp_cur INTO emp_rec;
 18                 EXIT WHEN emp_cur%NOTFOUND;
 19                 DBMS_OUTPUT.PUT_LINE(emp_rec.ename);
 20            END LOOP;
 21        END;
 22
 23      PROCEDURE close_cursor
 24      IS
 25        BEGIN
 26           CLOSE emp_cur;
 27        END;
 28  END test_pkg;
 29  /

Package body created.

--All In a Single Transaction
SQL> BEGIN
  2     test_pkg.open_cursor;
  3     test_pkg.fetch_cursor;
  4     test_pkg.close_cursor;
  5  END;
  6  /
KING
BLAKE
CLARK
JONES
SCOTT
FORD
SMITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER

PL/SQL procedure successfully completed.

--Separate Module Call One By One
SQL> BEGIN
  2     test_pkg.open_cursor;
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2     test_pkg.fetch_cursor;
  3  END;
  4  /
KING
BLAKE
CLARK
JONES
SCOTT
FORD
SMITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER

PL/SQL procedure successfully completed.

SQL> BEGIN
  2     test_pkg.close_cursor;
  3  END;
  4  /

PL/SQL procedure successfully completed.
--With Pragma SERIALLY_REUSABLE
SQL> CREATE OR REPLACE PACKAGE test_pkg
  2  IS
  3   PRAGMA SERIALLY_REUSABLE;
  4    CURSOR emp_cur IS SELECT * FROM emp;
  5
  6    PROCEDURE open_cursor;
  7
  8    PROCEDURE fetch_cursor;
  9
 10    PROCEDURE close_cursor;
 11
 12    var1 NUMBER := 1;
 13    var2 NUMBER := 2;
 14  END test_pkg;
 15  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY test_pkg
  2  IS
  3   PRAGMA SERIALLY_REUSABLE;
  4     var3 NUMBER := 3;
  5     var4 NUMBER := 4;
  6
  7     PROCEDURE open_cursor
  8     IS
  9       BEGIN
 10          OPEN emp_cur;
 11      END;
 12
 13      PROCEDURE fetch_cursor
 14      IS
 15       emp_rec emp%ROWTYPE;
 16        BEGIN
 17            LOOP
 18                 FETCH emp_cur INTO emp_rec;
 19                 EXIT WHEN emp_cur%NOTFOUND;
 20                 DBMS_OUTPUT.PUT_LINE(emp_rec.ename);
 21            END LOOP;
 22        END;
 23
 24      PROCEDURE close_cursor
 25      IS
 26        BEGIN
 27           CLOSE emp_cur;
 28        END;
 29  END test_pkg;
 30  /

Package body created.

--All In a Single Transaction
SQL> BEGIN
  2     test_pkg.open_cursor;
  3     test_pkg.fetch_cursor;
  4     test_pkg.close_cursor;
  5  END;
  6  /
KING
BLAKE
CLARK
JONES
SCOTT
FORD
SMITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER

PL/SQL procedure successfully completed.

--Separate Module Call One By One
SQL> BEGIN
  2     test_pkg.open_cursor;
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2     test_pkg.fetch_cursor;
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at "COURSE.TEST_PKG", line 18
ORA-06512: at line 2

SQL> BEGIN
  2     test_pkg.close_cursor;
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at "COURSE.TEST_PKG", line 27
ORA-06512: at line 2

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

  • Oracle Database Programming (SQL & PL/SQL)
  • Perl Programming
  • Unix Programming

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *