1.14 Packages

A package is a collection of PL/SQL objects that are grouped together. There are a number of benefits to using packages, including information hiding, object-oriented design, top-down design, object persistence across transactions, and improved performance.

Elements that can be placed in a package include procedures, functions, constants, variables, cursors, exception names, and TYPE statements (for associative arrays [formerly known as index-by tables], records, REF CURSORs, etc.).

1.14.1 Package Structure

A package can have two parts: the specification and the body. The package specification is required and lists all the objects that are publicly available (i.e., may be referenced from outside the package) for use in applications. It also provides all the information a developer needs in order to use objects in the package; essentially, it is the package's API.

The package body contains all the code needed to implement procedures, functions, and cursors listed in the specification, as well as any private objects (accessible only to other elements defined in that package), and an optional initialization section.

If a package specification does not contain any procedures or functions and no private code is needed, then that package does not need to have a package body.

The syntax for the package specification is:

CREATE [OR REPLACE] PACKAGE package_name  [ AUTHID { CURRENT_USER | DEFINER } ]  { IS | AS }    [definitions of public TYPEs    ,declarations of public variables, types, and        objects    ,declarations of exceptions    ,pragmas    ,declarations of cursors, procedures, and          functions    ,headers of procedures and functions] END [package_name];

The syntax for the package body is:

CREATE [OR REPLACE] PACKAGE BODY package_name     { IS | AS }    [definitions of private TYPEs    ,declarations of private variables, types, and        objects    ,full definitions of cursors    ,full definitions of procedures and functions] [BEGIN    executable_statements [EXCEPTION    exception_handlers ] ] END [package_name];

The optional OR REPLACE keywords are used to rebuild an existing package, preserving any EXECUTE privileges previously granted to other accounts. The declarations in the specifications cannot be repeated in the body. Both the executable section and the exception section are optional in a package body. If the executable section is present, it is called the initialization section and it executes only once the first time any package element is referenced during a session.

You must compile the package specification before the body specification. When you grant EXECUTE authority on a package to another schema or to PUBLIC, you are giving access only to the specification; the body remains hidden.

Here's an example of a package:

CREATE OR REPLACE PACKAGE time_pkg IS    FUNCTION  GetTimestamp  RETURN DATE;    PRAGMA RESTRICT_REFERENCES (GetTimestamp, WNDS);    PROCEDURE ResetTimestamp(new_time DATE DEFAULT        SYSDATE); END time_pkg; CREATE OR REPLACE PACKAGE BODY time_pkg IS    StartTimeStamp   DATE := SYSDATE;    -- StartTimeStamp is package data.    FUNCTION GetTimestamp RETURN DATE IS    BEGIN       RETURN StartTimeStamp;    END GetTimestamp;    PROCEDURE ResetTimestamp(new_time DATE DEFAULT SYSDATE)     IS    BEGIN       StartTimeStamp := new_time;    END ResetTimestamp; END time_pkg;  

1.14.2 Referencing Package Elements

The elements declared in the specification are referenced from the calling application via dot notation:

package_name.package_element

For example, the built-in package DBMS_OUTPUT has a procedure PUT_LINE, so a call to this package would look like this:

DBMS_OUTPUT.PUT_LINE('This is parameter data');

1.14.3 Package Data

Data structures declared within a package specification or body, but outside any procedure or function in the package, are package data. The scope of package data is your entire session, spanning transaction boundaries and acting as globals for your programs.

Keep the following guidelines in mind as you work with package data:

• The state of your package variables is not affected by COMMITs and ROLLBACKs.

• A cursor declared in a package has global scope. It remains OPEN until you close it explicitly or until your session ends.

• A good practice is to hide your data structures in the package body and provide "get and set" programs to read and write that data. This technique can help protect your data.

1.14.4 SERIALLY_REUSABLE Pragma

If you need package data to exist only during a call to the packaged functions or procedures, and not between calls of the current session, you can potentially save runtime memory by using the pragma SERIALLY_REUSABLE. After each call, PL/SQL closes the cursors and releases the memory used in the package. This technique is applicable only to large user communities executing the same routine. Normally, the database server's memory requirements grow linearly with the number of users; with SERIALLY_REUSABLE, this growth can be less than linear, because work areas for package states are kept in a pool in the Oracle's System Global Area (SGA) and are shared among all users. This pragma must appear in both the specification and the body, as shown here:

CREATE OR REPLACE PACKAGE my_pkg IS    PRAGMA SERIALLY_REUSABLE;    PROCEDURE foo; END my_pkg; CREATE OR REPLACE PACKAGE BODY my_pkg IS    PRAGMA SERIALLY_REUSABLE;    PROCEDURE foo IS    ... END my_pkg;

1.14.5 Package Initialization

The first time a user references a package element, the entire package is loaded into the SGA of the database instance to which the user is connected. That code is then shared by all sessions that have EXECUTE authority on the package.

Any package data are then instantiated into the session's User Global Area (UGA), a private area in either the System Global Area or the Program Global Area (PGA). If the package body contains an initialization section, that code will be executed. The initialization section is optional and appears at the end of the package body, beginning with a BEGIN statement and ending with the EXCEPTION section (if present) or the END of the package.

The following package initialization section runs a query to transfer the user's minimum balance into a global package variable. Programs can then reference the packaged variable (via the function) to retrieve the balance, rather than execute the query repeatedly:

CREATE OR REPLACE PACKAGE usrinfo IS    FUNCTION minbal RETURN VARCHAR2; END usrinfo; / CREATE OR REPLACE PACKAGE BODY usrinfo IS    g_minbal NUMBER; -- Package data    FUNCTION minbal RETURN VARCHAR2       IS BEGIN RETURN g_minbal; END; BEGIN  -- Initialization section    SELECT minimum_balance       INTO g_minbal       FROM user_configuration       WHERE username = USER; EXCEPTION    WHEN NO_DATA_FOUND    THEN g_minbal := NULL; END usrinfo;