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
