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.

Note

Standalone subprograms cannot be overloaded. Only packaged subprograms can be overloaded by their signatures.

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:

• It is the mandatory component of the package. A package cannot exist without its specification.

• It contains the prototypes of public constructs. The prototype is a forward declaration of the constructs that includes the declaration, header specification and signature information terminated by a semicolon. The subprograms constructs, once prototyped, should be defined in the package body section. The package specification cannot contain an executable section.

• These member constructs are visible within and outside the package. They can be invoked from outside the package by the privileged users.

  Note

  The public constructs of a package are accessed as [PACKAGE NAME].[CONSTRUCT].

• Valid package constructs can be PL/SQL types, variables, exceptions, procedures, and functions.

• If the package specification contains variables, they are implicitly initialized to NULL by Oracle

The characteristics of the package body are as follows:

• The package body contains the definition of the subprograms that were declared in the package specification.

• The package body can optionally contain local constructs. The accessibility of the local constructs is limited to the package body only.

• The package body is an optional component; a package can exist in a database schema without its package body.

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.