16.3.1.1 A package of exceptions

The exception handler package in the next example declares a set of programmer-defined exception numbers and exceptions to go with them. It also declares a PL/SQL table to hold the associated error messages (see Chapter 8, Exception Handlers , for more information about exceptions and the pragma statement, EXCEPTION_INIT).

PACKAGE exchdlr
IS
   en_general_error NUMBER := -20000;
   exc_general_error EXCEPTION;
   PRAGMA EXCEPTION_INIT (exc_general_error, -20000);

   en_must_be_eighteen  NUMBER := -20001;
   exc_must_be_eighteen EXCEPTION;
   PRAGMA EXCEPTION_INIT (exc_must_be_eighteen, -20001);

   max_error_number_used NUMBER := -20001;

   TYPE error_msg_tabtype IS TABLE OF VARCHAR2 (240)
      INDEX BY BINARY_INTEGER;
   error_msg_table error_msg_tabtype;

END exchdlr;

Because this package does not specify any cursors or modules, I do not need to create a body for the exception handler package. In Chapter 8 I include a version of this package that does, in fact, specify two procedures in the package. That version does need a package body).

16.3.1.2 A package of magic values

A magic value is a literal that has special significance in a system. These values might be type codes or validation limits. Your users will tell you that these magic values never change. "I will always have only 25 line items in my profit-and-loss," one will say. "The name of the parent company," swears another, "will always be ATLAS HQ." Don't take these promises at face value, and never code them into your programs. Instead of writing code like this:

IF footing_difference BETWEEN 1 and 100
THEN adjust_line_item;
END IF;

IF cust_status = 'C'
THEN
   reopen_customer;
END IF;

you should instead replace the magic values with named constants, as follows:

IF footing_difference BETWEEN min_difference and max_difference
THEN adjust_line_item;
END IF;

IF cust_status = closed_status
THEN
   reopen_customer;
END IF;

The same magic values often appear in many different modules. Rather than declaring them repeatedly in each module, these magic values should be treated as global data in your application. And where can you create global data in PL/SQL? That's right, in a package! The bodiless package shown in the next example contains all the magic values in my application:

PACKAGE config_pkg
IS
   closed_status     CONSTANT VARCHAR2(1) := 'C';
   open_status       CONSTANT VARCHAR2(1) := 'O';
   active_status     CONSTANT VARCHAR2(1) := 'A';
   inactive_status   CONSTANT VARCHAR2(1) := 'I';

   min_difference    CONSTANT NUMBER := 1;
   max_difference    CONSTANT NUMBER := 100;

   earliest_date     CONSTANT DATE := SYSDATE;
   latest_date       CONSTANT DATE := ADD_MONTHS (SYSDATE, 120);

END config_pkg;

Using this package, my two IF statements above now become:

IF footing_difference
   BETWEEN config_pkg.min_difference and config_pkg.max_difference
THEN
   adjust_line_item;
END IF;

IF cust_status = config_pkg.closed_status
THEN
   reopen_customer;
END IF;

Notice that when you reference a package element you must use dot notation in the format package_name.object_name so the compiler can resolve the reference to the object's name. This is similar to prefacing a reference to a GLOBAL variable in Oracle Forms with the word GLOBAL, as in :GLOBAL.system_name.

If any of my magic values ever change, I need to modify only the assignment to the appropriate constant in the configuration package. I do not need to change a single program module. Just about every application I have reviewed (and many that I have written) mistakenly includes hardcoded magic values in the program. In every single case (especially those that I myself wrote), the developer had to make repeated changes to the programs, both during development and maintenance phases. It was often a headache, sometimes a nightmare; I cannot emphasize strongly enough the importance of consolidating all magic values into a single package, with or without a body.

16.3.1.3 Top-down design with bodiless packages

There is another motivation for writing a package without a body: you don't yet know what the body is going to look like. If you follow a top-down design methodology, you start with a general description of an application and gradually decompose into separate functional areas, programs, or individual statements. With the package structure you can immediately translate high-level refinements into code, while at the same time postponing a resolution of the actual implementation of that code. You can even compile references to unimplemented package modules as long as that module specification is available in a package specification.

Suppose I need to build an application to track calls about product quality. The functional areas of this application include call entry/maintenance, data validation, call analysis, and call assignment, and are represented in the packages shown below:

• Package to maintain support calls:

  PACKAGE support
  IS
     PROCEDURE add_call (call_id IN INTEGER);
     PROCEDURE remove_call (call_id IN INTEGER);
  END support;

• Package to perform analysis on call workload:

  PACKAGE workload
  IS
     FUNCTION average (dept_id IN INTEGER)
        RETURN NUMBER;
     FUNCTION operator (operator_id IN INTEGER)
        RETURN NUMBER;
  END workload;

Notice that I have only created package specifications. There is no body yet for these modules. Still, I can now implement the assigncall procedure below, referencing my "stubs." This procedure will compile successfully.

PROCEDURE assigncall
   (call_id IN INTEGER,
    operator_id IN INTEGER,
    dept_id IN INTEGER)
IS
BEGIN
   IF workload.operator (operator_id) <
      workload.average (dept_id)
   THEN
      support.add_call (call_id);
   END IF;
END;

How can PL/SQL do this? It all goes back to the public/private boundary of packages. The package provides all the information you need to call package modules: name, parameters, RETURN type if a function. You should not need to know how a module is implemented. Conversely, the implementation of a module can change, and it should not affect your code -- unless the module's header was affected (a parameter was removed, for example). When you write your own programs, the only thing PL/SQL needs to know at compile time is whether your call to a package module conforms to its specification. Of course, when you want to actually execute your application, you should throw some code into the package body.

The separation of specification and body in packages offers you tremendous flexibility in performing high-level design and implementation of your complex applications. See how great packages are? And we haven't even gotten to the body of the package yet!