1.3.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 implementation 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.

1.3.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 your entities (whether they are database tables or memory-based structures), and access to them, in the package. Since this is the only way to work with that entity, you have in essence created an abstracted and encapsulated object.

1.3.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 or implementation. Furthermore and most remarkably, programs that call packaged modules also compile successfully -- even if nothing more than the specification has been defined.

1.3.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 have 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 many limitations (e.g., GLOBAL variables are always represented as fixed-length CHAR variables with a length of 254), but they sure can be useful. Package-based data gets around all these limitations.

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).

And your global data doesn't consist merely of scalar data like numbers. If, for example, 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.

Finally, package variables can carry data across the boundaries of transactions, since they are tied to the session itself and not to a transaction.

1.3.5 Guaranteeing Transaction Integrity

The RDBMs and SQL language give you the ability to tightly control access to, and changes in, any particular table. With the GRANT command you can, for example, make sure that only certain roles and users have the ability to perform an UPDATE on a given table. But this GRANT statement cannot make sure that the UPDATEs performed by a user or application that affect multiple tables conform to all complex business rules.

In a typical banking transaction, for example, you might need to transfer funds from account A to account B. The balance of account B must be incremented, and that of account A decremented. Table access is necessary, but not sufficient, to guarantee that both of these steps are always performed by all programmers who write code to perform a transfer. With stored code in general, and packages in particular, you can guarantee that a funds transfer either completes successfully or is completely rolled back -- regardless of who executes the process.

The secret to achieving this level of transaction integrity is the execute authority concept. Instead of granting the authority to update a table to a role or user, you grant privileges to that role/user only to execute a procedure. This procedure controls and provides access to the underlying data structures. The procedure is owned by a separate Oracle RDBMs account, which, in turn, is granted the actual update privileges on those tables needed to perform the transaction. The procedure therefore becomes the gatekeeper for the transfer transaction. The only way a program (whether it is an Oracle Forms application or a Pro*C executable) can execute the transfer is through the procedure, thus guaranteeing transaction integrity.

1.3.6 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 System Global Area, or SGA, of the RDBMs). All other package elements are thereby made immediately available for future calls to the package. The PL/SQL runtime engine 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 a 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 changes (for example, a table's structure changes), then all programs that rely on that object are flagged as invalid. The database then automatically recompiles these invalid programs when they are referenced next.

You can limit the need for 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 brief review of the benefits of packages should help focus your interest on this fascinating and powerful element of the PL/SQL language.