A PL/SQL package

A PL/SQL package encapsulates multiple PL/SQL constructs under a single unit. The PL/SQL constructs can be subprograms, cursors, variables, and exceptions. As a schema object, a PL/SQL package demonstrates the principles of logic hiding, encapsulation, and subprogram overloading.

The following diagram shows the advantages of a package:

A package has two components—the package specification and package body. While the package specification contains the prototype of public constructs, the package body contains the definition of public as well as private (local) constructs.

The characteristics of the package specification are as follows:

The characteristics of the package body are as follows:

The syntax for creating a package is as follows:

CREATE [OR REPLACE] PACKAGE [NAME] IS
  [PRAGMA]
  [PUBLIC CONSTRUCTS]
END;

CREATE [OR REPLACE] PACKAGE BODY [NAME] IS
  [LOCAL CONSTRUCTS]
  [SUBPROGRAM DEFINITION]
  [BEGIN]
END;

Note the optional BEGIN section in the package body. It is optional, but gets executed only the first time the package is referenced. It is used to initialize global variables.

A package can be compiled with its specification component alone. In such cases, packaged program units cannot be invoked as their executable logic has not been defined yet.

The compilation of a package with a specification and body ensures the concurrency between the program units prototyped in the specification and the program units defined in the package body. All packaged program units are compiled in a single package compilation. If a package is compiled with errors, it is created as an invalid object in the database schema. You can query the STATUS column to check the current status of an object in the USER_OBJECTS, ALL_OBJECTS, or DBA_OBJECTS dictionary views.