Lab 14.1 The Benefits of Utilizing Packages

Lab Objectives

After this Lab, you will be able to:

Create Package Specifications

Create Package Bodies

Call Stored Packages

Create Private Objects

Create Package Variables and Cursors

There are numerous benefits of using packages as a method to bundle your functions and procedures, the first being that a well-designed package is a logical grouping of objects ”such as functions, procedures, global variables, and cursors. All of the code (parse tree and pseudocode [ p-code ]) is loaded on the first call of the package. This means that the first call to the package is very expensive (involves a lot of processing on the server), but all subsequent calls will result in an improved performance. Packages are therefore often used in applications where procedures and functions are used repeatedly.

There is also an additional level of security using packages. When a user executes a procedure in a package (or stored procedures and functions), the procedure operates with the same permissions as its owner. Packages also allow the creation of private functions and procedures, which can only be called from other functions and procedures in the package. This enforces information hiding. The structure of the package also encourages top-down design.

The Package Specification

The package specification contains information about the contents of the package, but not the code for the procedures and functions. It also contains declarations of global/public variables. Anything placed in the declarative section of a PL/SQL block may be coded in a package specification. All objects placed in the package specification are called public objects. Any function or procedure not in the package specification but coded in a package body is called a private function or procedure.

The Package Body

The package body contains the actual executable code for the objects described in the package specification. The package body contains code for all procedures and functions described in the specification and may additionally contain code for objects not declared in the specification; the latter type of packaged object is invisible outside the package and is referred to as hidden. When creating stored packages, the package specification and body can be compiled separately.

Rules for the Package Body

There are a number of rules that must be followed in package body code: (1) There must be an exact match between the cursor and module headers and their definitions in package specification; (2) do not repeat declaration of variables, exceptions, type, or constants in the specification again in the body; and (3) any element declared in the specification can be referenced in the body.

Referencing Package Elements

Use the following notation when calling packaged elements from outside of the package: package_name.element .

You do not need to qualify elements when declared and referenced inside the body of the package or when declared in a specification and referenced inside the body of the same package.