1.1 What Is a PL/SQL Package?

A package is a collection of PL/SQL elements that are "packaged" or grouped together within a special BEGIN-END syntax, a kind of "meta-block." Here is a partial list of the kinds of elements you can place in a package:

• Cursors

• Variables (scalars, records, tables, etc.) and constants

• Exception names and pragmas for associating an error number with an exception

• PL/SQL table and record TYPE statements

• Procedures and functions

Packages are among the least understood and most underutilized features of PL/SQL. That's a shame because the package structure is also one of the most useful constructs for building well-designed PL/SQL-based applications. Packages provide a structure to organize your modules and other PL/SQL elements. They encourage proper structured programming techniques in an environment that often befuddles the implementation of structured programming. When you place a program unit into a package you automatically create a "context" for that program. By collecting related PL/SQL elements in a package, you express that relationship in the very structure of the code itself. Packages are often called "the poor man's objects" because they support some, but not all, object-oriented rules.

The PL/SQL package is a deceptively simple, yet powerful construct. It consists of up to two distinct parts: the specification and the body.

• The package specification, which defines the public interface (API) of the package: those elements that can be referenced outside of the package.

• The package body, which contains the implementation of the package and elements of the package you want to keep hidden from view.

In just a few hours you can learn the basic elements of package syntax and rules; there's not all that much to it. You can spend weeks and months, however, uncovering all the nuances and implications of the package structure.

Oracle Corporation itself uses the package construct to define and extend the PL/SQL language. In fact, the most basic operators of the PL/SQL language, such as the + and LIKE operators and the INSTR function, are all defined in a special package called STANDARD.[1] Packages will, without doubt, be the preferred method of delivering new functionality in PL/SQL in the coming decade. Just consider PL/SQL packages in the Oracle Web Agent: these add-ons provide a powerful interface between World Wide Web pages/HTML and the Oracle database, allowing you to construct Oracle-aware WWW pages more easily.

[1] If Oracle believes that packages are the way to go when it comes to building both fundamental and complex programs, don't you think that you could benefit from the same?