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.

Note

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

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:

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

• Package specification contains the prototypes of the constructs. The prototype is the forward declaration of the constructs which would be referenced later in the package body. The subprogram (procedure and function) prototype includes the signature information with a semicolon. The subprograms, once prototyped, must have their definition in the package body section. The package specification cannot contain an executable section.

• These member constructs enjoy their visibility 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].

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

• If 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 which were declared in package specification.

• The package body can optionally contain local constructs. The visibility scope 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 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]
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.