16. Packages

Contents:
The Benefits of Packages
Overview of Package Structure
The Package Specification
The Package Body
Package Data
Package Initialization

A package is a collection of PL/SQL objects 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 objects you can place in a package:

Cursors
Variables (scalars, records, tables, etc.)
Constants
Exception names
PL/SQL table and record TYPE statements
Procedures
Functions

Packages are among the least understood and most underutilized features of PL/SQL. That is 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 in which you can 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.

Oracle Corporation itself uses the package construct to extend the PL/SQL language. Appendix C, Built-In Packages , contains descriptions of many of these predefined packages. 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. 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?

Packages are, by nature, highly modular. 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. For example, packages allow you to encapsulate and abstract your data and functions.

The PL/SQL package is a deceptively simple, powerful construct. You can in just a few hours learn the basic elements of package syntax and rules; there's not all that much to it. You can spend days and weeks, however, uncovering all the nuances and implications of the package structure. This chapter -- and the next one filled with examples of packages -- will help you absorb the features and benefits of the PL/SQL package more rapidly.

16.1 The Benefits of Packages

Before we explore all the aspects of working with packages, let's review some of the most important benefits of the package:

16.1.1 Enforced Information Hiding

When you build a package, you decide which of the package elements are public (can be referenced outside of the package) and which are private (available only within the package itself). You also can restrict access to the package to only the specification. In this way, you use the package to hide the implementational details of your programs. This is most important when you want to isolate the most volatile aspects of your application, such as platform dependencies, frequently changing data structures, and temporary workarounds.

16.1.2 Object-Oriented Design

While PL/SQL does not yet offer full object-oriented capabilities, packages do offer the ability to follow many object-oriented design principles. The package gives developers very tight control over how the modules and data structures inside the package can be accessed.

You can, therefore, embed all the rules about and access to your entities (whether they are database tables or memory-based structures) in the package. Because this is the only way to work with that entity, you have in essence created an abstracted and encapsulated object.

16.1.3 Top-Down Design

A package's specification can be written before its body. You can, in other words, design the interface to the code hidden in the package (the modules, their names, and their parameters) before you have actually implemented the modules themselves. This feature dovetails nicely with top-down design, in which you move from high-level requirements to functional decompositions to module calls.

Of course, you can design the names of standalone modules just as you can the names of packages and their modules. The big difference with the package specification is that you can compile it even without its body. Furthermore, and most remarkably, programs that call packaged modules will compile successfully -- as long as the specification compiles.

16.1.4 Object Persistence

PL/SQL packages offer the ability to implement global data in your application environment. Global data is information that persists across application components; it isn't just local to the current module. If you designed screens with SQL*Forms or Oracle Forms, you are probably familiar with its GLOBAL variables, which allow you to pass information between screens. Those globals have their limitations (GLOBAL variables are always represented as fixed-length CHAR variables with a length of 254), but they sure can be useful.

Objects declared in a package specification (that is, visible to anyone with EXECUTE authority on that package) act as global data for all PL/SQL objects in the application. If you have access to the package, you can modify package variables in one module and then reference those changed variables in another module. This data persists for the duration of a user session (connection to the database).

If a packaged procedure opens a cursor, that cursor remains open and is available to other packaged routines throughout the session. You do not have to explicitly define the cursor in each program. You can open it in one module and fetch it in another module. In addition, package variables can carry data across the boundaries of transactions, because they are tied to the session itself and not to a transaction.

16.1.5 Performance Improvement

When an object in a package is referenced for the first time, the entire package (already compiled and validated) is loaded into memory (the Shared Global Area [SGA] of the RDBMS). All other package elements are thereby made immediately available for future calls to the package. PL/SQL does not have to keep retrieving program elements or data from disk each time a new object is referenced.

This feature is especially important in a distributed execution environment. You may reference packages from different databases across a local area or even a wide area network. You want to minimize the network traffic involved in executing your code.

Packages also offer performance advantages on the development side (with potential impact on overall database performance). The Oracle RDBMS automatically tracks the validity of all program objects (procedures, functions, packages) stored in the database. It determines what other objects that program is dependent on, such as tables. If a dependent object such as a table's structure changes, for example, then all programs that rely on that object are flagged as invalid. The database then automatically recompiles these invalid programs before they are used.

You can limit automatic recompiles by placing functions and procedures inside packages. If program A calls packaged module B, it does so through the package's specification. As long as the specification of a packaged module does not change, any program that calls the module is not flagged as invalid and will not have to be recompiled.

This chapter should provide you with all the information and examples you need to put packages to work immediately in your applications. If you are still unsure about packages after reading it, try out a couple of small packages. Test those hard-to-believe features like global package data to prove to yourself that they really work as advertised. Examine carefully the examples in Chapter 18, Object Types . Do whatever you need to do to incorporate packages into every level of your application, from database server to client applications.