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). After this call, the storage for the package variables can be reused, reducing the memory overhead for long-running sessions.
This pragma is appropriate for packages that declare large temporary work areas that are used only once in the same session.
Signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They pass information to the compiler.
The global memory for serially reusable packages is pooled in the System Global Area (SGA), not allocated to individual users in the User Global Area (UGA). That way, the package work area can be reused. When the call to the server ends, the memory is returned to the pool. Each time the package is reused, its public variables are initialized to their default values or to
Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are called from SQL statements. If you try, the database generates an error.
SERIALLY_REUSABLE pragma can appear in the specification of a bodiless package, or in both the specification and body of a package. The pragma cannot appear only in the body of a package.
Example 13-5 creates a serially reusable package.
CREATE PACKAGE pkg1 IS PRAGMA SERIALLY_REUSABLE; num NUMBER := 0; PROCEDURE init_pkg_state(n NUMBER); PROCEDURE print_pkg_state; END pkg1; / CREATE PACKAGE BODY pkg1 IS PRAGMA SERIALLY_REUSABLE; PROCEDURE init_pkg_state (n NUMBER) IS BEGIN pkg1.num := n; END; PROCEDURE print_pkg_state IS BEGIN DBMS_OUTPUT.PUT_LINE('Num: ' || pkg1.num); END; END pkg1; /
See Also:Oracle Database Advanced Application Developer's Guide for more information about serially reusable PL/SQL packages