Picking Your Packages

Know when—and when not—to package your PL/SQL.

May/June 2005

The Oracle PL/SQL language is a joy to use; it is a straightforward and eminently readable programming tool. It is also focused like a laser beam on its particular niche: performing operations against the underlying Oracle database.

One of the most important and useful elements of PL/SQL is the package. In the early days of PL/SQL, many developers were unfamiliar with the package. They knew how to work with procedures, functions, and triggers, but packages presented another level of complexity and abstraction.

Today, the package has moved into mainstream PL/SQL programming. It is widely accepted as a fundamental building block of PL/SQL applications. Yet I still receive a steady stream of questions from PL/SQL programmers about the applicability of packages and some concerns about their usage.

This article reviews the underlying concepts and key benefits of PL/SQL packages and then explores some of the less familiar nuances of package behavior. Finally, I look at a situation where it makes sense to avoid packages.

Let's first review some of the most important components and concepts that underlie the package structure in PL/SQL.

Specification and body. A package is composed of (as many as) two different chunks of code: the package specification and the package body.

The package specification contains the definition of all the publicly available elements in the package that may be referenced outside of the package. It tells you what is available for use in the package and contains no information about the implementation of the package's programs (unless you add explanatory comments). If a specification is well designed, a developer can learn from it everything necessary to use the package.

The package body contains all the code required to implement elements defined in the package specification. The body may also contain private elements, which do not appear in the specification and, therefore, cannot be referenced outside of the package. The body of the package resembles a standalone program's declaration section. It contains both declarations of variables and the definitions of all package programs. The package body may also contain an execution section, which is called the initialization section , because it runs only once, to initialize the package.

Keeping implementation (body) separate from interface (specification) is critical, given the amount of change our applications experience over time. Our application users are constantly changing their minds, and Oracle is constantly improving PL/SQL and the database. These factors conspire to keep us on our toes and on the lookout for ways to improve our code.

Public and private. Public code is defined in the package specification and is available to any schema that has EXECUTE authority on the package (including the schema that owns the package). Private code, on the other hand, is defined in and visible only from within the package. External programs using the package cannot see or use private code.

When you build a package, you decide which of the package elements are public and which are private. You also can hide all the details of the package body from the view of other schemas and developers. In this way, you use the package to hide the implementation details of your programs. This is important when you want to isolate the most volatile aspects of your application, such as platform dependencies, frequently changing data structures, and temporary workarounds.

Initialization. Initialization should not be a new concept for a programmer. In the context of packages, however, initialization takes on a specific meaning. Rather than initializing the value of a single variable, you initialize the entire package with code that is as simple or as complicated as is required by your application. Oracle Database makes sure that the package is initialized only once per session. If you wrote code to initialize package data structures yourself, the code would become unwieldy or might run more often than necessary.

Session persistence. As a database programmer, you should be familiar with the concept of persistence. After all, a database is all about persistence. For example, I insert a row into the database on Monday, fly to the Bahamas for the rest of the week, and when I return to work on the following Monday, my row is still in the database. Another kind of persistence is session persistence , which PL/SQL packages support. This means that if I connect to an Oracle database (establish a session) and execute a program that assigns a value to a package-level variable (a variable declared in a package, outside of any program in the package), that variable is set to persist for the length of my session, and it retains its value even if the program that performed the assignment has ended.

The most important benefits of packages flow directly from the concepts covered in the previous section.

Collect related items. One of the nicest things about the package is that it provides a container in which to place related items. Rather than have 10 or 20 standalone programs all offering various mortgage calculations, I can put them together in a mortgage_calc package.

This logical grouping makes it easier for members of my team to find code they need to run. And when another programmer implements yet another mortgage calculation, that person knows exactly where to put it. Many of these calculation routines are likely to share common, internal functionality. All of the programmers can isolate this common code in programs defined privately in the package body, thus encouraging reuse of existing code.

Any reasonably complicated application will have hundreds of individual program units. If they are all implemented as standalone programs, you will be overwhelmed and bewildered by the contents of the object browser (that is, the contents of the USER_OBJECTS data dictionary view) of your favorite integrated development environment (IDE). Packages help you scope down the number of program units and make your code base more manageable.

Change implementations painlessly. One of the most important benefits of separating the public interface of a package (its specification) from its implementation (the body) is that you can change the implementation or package body without affecting the application code that is using the package. You will not, in fact, even have to recompile that application code—as long as the package specification is not modified or recompiled. Any code dependent on that package remains valid.

This fact gives you a tremendous amount of freedom to explore new options in PL/SQL, to improve both the readability of your code and also its performance, without having to worry about effects rippling through dozens or hundreds of programs.

As mentioned earlier, the initialization section of a package will run just once per session, to run whatever setup logic is necessary for the package. This raises the logical question: What happens when initialization fails? Let's look at an example.

Consider the very simple package in Listing 1. The valerr package specification contains a single function, which gets the value of g_private . The variable g_private is defined in the body at the package level, at which point it is assigned the value of abc.

Code Listing 1: The valerr package

CREATE OR REPLACE PACKAGE valerr
IS
   FUNCTION private_variable RETURN VARCHAR2;
END valerr;
/
CREATE OR REPLACE PACKAGE BODY valerr
IS
   g_private VARCHAR2(1) := 'abc';
   FUNCTION private_variable RETURN VARCHAR2
   IS
   BEGIN
      RETURN g_private;
   END private_variable;
BEGIN
   DBMS_OUTPUT.PUT_LINE ('Before I show you v...');
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.PUT_LINE ('Trapped the error!');

END valerr;
/

The valerr package also contains an initialization section that will display a message. Finally, there is an exception section for the package that displays a different message.

As you might suspect from the name of this package, when I try to assign the default value to g_private , Oracle Database raises the VALUE_ERROR exception, ORA-06502, because the variable can hold only one letter and I try to assign it three. But what happens to that exception after it is raised?

When I run this program immediately after I compile the package, I see:

SQL> exec DBMS_OUTPUT.PUT_LINE
(valerr.private_variable)
BEGIN DBMS_OUTPUT.PUT_LINE
(valerr.private_variable);
END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or
value error: character string
buffer too small
ORA-06512: at "SCOTT.VALERR", line 3
ORA-06512: at line 1

At first glance, this is very nonintuitive. I have an exception section in my package that traps "any error" (with WHEN OTHERS ) and should display the message "Trapped the error!" Instead, my error goes unhandled.

The reason for this behavior is that the exception section shown in the package body is able to handle only errors that are raised in the executable or initialization section of the package. Because the attempt to assign a value to that package variable raises an error, the initialization section is never executed at all . Thus, the exception section doesn't come into play and the error goes unhandled.

Even more interestingly, if I attempt to run this program again, I do not get the error. Instead, the function executes and returns the value of g_private , which is NULL:

SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE
  3   ('Value='||valerr.private_variable);
  4   END;
SQL> /
Value=

And now we have come to the crux of the issue with package initialization failures: even if the package fails to initialize properly, Oracle marks the package as initialized.

This can be a very tricky situation to detect, because you will get a single, nonrepeating failure. In fact, you may be able to continue to run your application and not see any obvious problems, because the failure can be isolated to a single value, leaving all the rest of the code in the package to run properly.

You can, however, obtain a repeat of the error by forcing a reinitialization of the package. Simply reconnect to Oracle Database, or recompile your package.

Generally, if you find yourself facing this kind of scenario—the error occurs once, with first use, but does not (immediately) repeat itself in your session—you should think back to the packages you have recently modified and focus in particular on the declaration sections within the package specification or body.

Better yet, you should consider an alternative way of implementing assignments of default values for package data. Rather than assign them in the declaration section, create a separate initialization procedure that contains all the setup or initialization logic for the package. Put the exception handler in that procedure. Call the initialization procedure in the initialization section of the package. This approach is shown in Listing 2.

Code Listing 2: Using an initialization procedure in the package body

CREATE OR REPLACE PACKAGE BODY valerr
IS
   g_private   VARCHAR2 (1);
   FUNCTION private_variable
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN g_private;
   END private_variable;
   PROCEDURE initialize
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('Before I show you v...');
      g_private := 'abc';
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Trapped the error!');
   END initialize;
BEGIN
   initialize;
END valerr;
/

With the initialization program in place, a call to the private_variable function immediately after compilation (thus ensuring that initialization takes place) results in very different behavior:

SQL> @valerr.pks
SQL> exec DBMS_OUTPUT.PUT_LINE
(valerr.private_variable)
Before I show you v...
Trapped the error!

In other words, the initialization section was run this time, and the error was trapped.

The bottom line: Shift all assignment of default values to the initialization phase of package execution. Furthermore, encapsulate all initialization actions into a single procedure, which you then call in the initialization section.

Given all the wonderful features and benefits of packages, I recommend that you use packages as the default program unit for PL/SQL development. In other words, avoid standalone procedures and functions. Start with and stick with packages unless you have a specific reason to change (one such scenario is explored in the next section).

Collect related functionality under the single "umbrella" of a package name. Share session-specific and persistent data between programs by defining them in the package body. Define clear, consistent, easy-to-use interfaces in the package specification, relieving your fellow programmers of the need to deal with any of the underlying details.

For all the benefits and usefulness of packages, there are some aspects of packages that can give pause—and sometimes, though rarely, argue against the use of packages. Let's take a closer look.

Suppose I have a package of commonly used utilities, chip_util , for a potato chip manufacturer. It contains, in particular, the company_tagline function, which returns this potato chip brand's marketing tagline ("Everybody wants more!"). This tagline appears at the bottom of company e-mails, inside reports, on screens, and so on. It is called by hundreds of programs in the system, and it never changes (this is not a company that spends a whole lot of money on marketing!). Listing 3 shows what the chip_util package might look like.

Code Listing 3: The chip_util package

CREATE OR REPLACE PACKAGE chip_util
IS
   FUNCTION company_tagline RETURN VARCHAR2;
   FUNCTION max_chip_diameter (brand_in IN VARCHAR2) RETURN NUMBER;
   FUNCTION to_metric (weight_in in NUMBER) RETURN NUMBER;
   ...
   FUNCTION nutrition_label_template RETURN VARCHAR2;
END chip_util;
/
CREATE OR REPLACE PACKAGE BODY chip_util
IS
   FUNCTION company_tagline RETURN VARCHAR2
   IS
   BEGIN
      RETURN 'Everybody wants more!';
   END company_tagline;
   ... all the rest of the programs ...
END chip_util;
/

The chip_util package is constantly updated with new utilities, thus requiring the recompilation of the chip_util package specification. This recompilation, in turn, forces the invalidation and recompilation of all those programs that call chip_util.company_tagline —yet that program itself has not changed.

In this scenario, I would be better off taking the company_tagline function out of my package and defining it instead as a standalone function, as shown below:

CREATE OR REPLACE FUNCTION
company_tagline RETURN VARCHAR2
IS
BEGIN
  RETURN 'Everybody wants more!'
END company_tagline;
/

More generally, consider following these guidelines to minimize the need for recompilation of your package code:

• Isolate and separate functionality that rarely, if ever, changes, from those areas of your application that change frequently. This might result in a small number of standalone programs, or you might end up with a package of "static" code.

• Avoid program dependency bottlenecks in your code base. If you create packages that are referenced widely in your application, do everything you can to avoid the need to recompile those programs. For example, rather than declare constants in the package specification, define a function that returns the constant value from within the package body. That way, you can always change the value without having to recompile the specification.

Beyond the issue of recompilation, you might also choose to bypass a package when you need the capabilities of an object-oriented structure in your application. Using Oracle's object type—initially introduced in Oracle8 and greatly enhanced with support for inheritance in Oracle9i—would in such a situation be a better choice.

Without a doubt, packages serve as the cornerstone of application development in the world of Oracle PL/SQL. They help you organize your code logically, leverage session-persistent data, and provide clean interfaces to underlying functionality.

Watch out, however, for complications with package initialization. And recognize that no matter how compelling a package may be, there are times when it will still make sense to rely on standalone procedures and functions.