PL/SQL packages

Packages are the database objects which behave as libraries and grounds on the principle of encapsulation and data hiding. A package is privileged to contain a variety of constructs such as subprograms, variables, cursors, exceptions, and variables. In addition, it enjoys multiple add-on features such as subprogram overloading, public and private member constructs, and so on.

The following diagram shows the advantages of a package:

A package has two components—package specification and package body. While 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 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]
END;

Note the optional BEGIN-END block in the package body. It is optional, but gets executed only the first time the package is referenced. They are used for initialization of 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 specification and body ensures the concurrency between the program units prototyped in the specification and the program units defined in the package body. All the packaged program units are compiled in the single package compilation. If the package is compiled with errors, it is created as an invalid object in the database. The USER_OBJECTS dictionary view is used to query the status of a schema object. The STATUS column in the view shows the current status as VALID or INVALID.