1.14.1 Overview of 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 (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 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 ]   -- Oracle8i
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 its privileges. 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 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;
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 IS
   BEGIN
      StartTimeStamp := SYSDATE;
   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; it spans transaction boundaries, 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 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 protects your data.

1.14.4 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 UGA (User Global Area), a private area in either the SGA or PGA (Program Global Area). 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 executing 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;