Serializable Packages

As we have seen, package data by default persists for your entire session (or until the package is recompiled). 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. In particular, I can inadvertently leave packaged cursors open, causing "already open" errors in other programs.

· My 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 (if one exists), 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.

To see the effects of this pragma, consider the following book_info package. I have created two separate programs: one to fill a list of books and another to show that list.

/* File on web: serialpkg.sql */CREATE OR REPLACE PACKAGE book_infoIS PRAGMA SERIALLY_REUSABLE; PROCEDURE fill_list; PROCEDURE show_list;END;

As you can see in the following package body, that list is declared as a private, but global, associative array:

/* File on web: serialpkg.sql */CREATE OR REPLACE PACKAGE BODY book_infoIS PRAGMA SERIALLY_REUSABLE; TYPE book_list_t IS TABLE OF books%ROWTYPE INDEX BY BINARY_INTEGER; my_books book_list_t; PROCEDURE fill_list IS BEGIN FOR rec IN (SELECT * FROM books WHERE AUTHOR LIKE '%FEUERSTEIN%') LOOP my_books (NVL (my_books.LAST, 0) + 1) := rec; END LOOP; END fill_list; PROCEDURE show_list IS BEGIN IF my_books.COUNT = 0 THEN DBMS_OUTPUT.put_line ('** No books to show you...'); ELSE FOR indx IN my_books.FIRST .. my_books.LAST LOOP DBMS_OUTPUT.put_line (my_books (indx).title); END LOOP; END IF; END show_list;END;

To see the effect of this pragma, I fill and then show the list. In my first approach, these two steps are done in the same block, so the collection is still loaded and can be displayed:

SQL> BEGIN 2 DBMS_OUTPUT.put_line ( 3 'Fill and show in same block:' 4 ); 5 book_info.fill_list; 6 book_info.show_list; 7 END; 8 /Fill and show in same block:Oracle PL/SQL ProgrammingOracle PL/SQL Best PracticesOracle PL/SQL Built-in Packages

In my second attempt, I fill and show the list in two separate blocks. As a result, my collection is now empty:

SQL> BEGIN 2 DBMS_OUTPUT.put_line ('Fill in first block'); 3 book_info.fill_list; 4 END; 5 /Fill in first block SQL> BEGIN 2 DBMS_OUTPUT.put_line ('Show in second block:'); 3 book_info.show_list; 4 END; 5 /Show in second block:** No books to show you...

Here are some things to keep in mind for serialized packages:

· The global memory for serialized packages is allocated in the System Global Area (SGA), not in the user's User Global Area (UGA). This approach allows the package work area to be reused. Each time the package is reused, its public variables are initialized to their default values or to NULL, and its initialization section is re-executed.

· The maximum number of work areas needed for a serialized package is the number of concurrent users of that package. The increased use of SGA memory is offset by the decreased use of UGA or program memory. Finally, Oracle ages out work areas not in use if it needs to reclaim memory from the SGA for other requests.