Best Practices for PL/SQL Variables - Packages should have a strong sense of personal space

Control access to package data with “get and set” modules.

Problem: Data structures declared in a package specification may end up bypassing business rules.

Data structures (scalar variables, collections, cursors) declared in the package specification (not within any specific program) are able to be referenced directly from any program run from a session with EXECUTE authority on the package. This is almost always a bad idea and should be avoided.

Solution: Declare data in the package body, and hide the data structures via functions in the package specification.

Instead, declare all package-level data in the package body and provide “get and set” programs—a function to GET the value and a procedure to SET the value—in the package specification. Developers can then access the data through these programs, and automatically follow whatever rules you establish for manipulating that data.

Suppose that I’ve created a package to calculate fines for overdue books. The fine is, by default, $.10 per day, but it can be changed according to this rule: the fine can never be less than $.05 or more than $.25 per day. Here’s my first version:

  PACKAGE overdue_pkg
  IS
     g_daily_fine NUMBER DEFAULT .10;
     FUNCTION days_overdue (isbn_in IN book.isbn%TYPE)
        RETURN INTEGER;

     -- Relies on g_daily_fine for calculation
     FUNCTION fine (isbn_in IN book.isbn%TYPE)
        RETURN INTEGER;
  END overdue_pkg;

You can easily see the problem with this package in the following block:

  BEGIN
     overdue_pkg.g_daily_fine := .50;

     DBMS_OUTPUT.PUT_LINE ('Your overdue fine is ' ||
         overdue_pkg.fine (' 1-56592-375-8'));
  END;

Here I bypassed the business rule and applied a daily fine of $.50! By “publishing” the daily fine variable, I lost control of my data structure and the ability to enforce my business rules.

The following rewrite of overdue_pkg (available on the book’s web site) fixes the problem; for the sake of the trees, I show only the replacement of the g_daily_fine variable with its “get and set” programs:

  PACKAGE overdue_pkg
  IS
     PROCEDURE set_daily_fine (fine_in IN NUMBER);
     FUNCTION daily_fine RETURN NUMBER;

and the implementation:

  PACKAGE BODY overdue_pkg
  IS
     g_daily_fine NUMBER DEFAULT .10;

     PROCEDURE set_daily_fine (fine_in IN NUMBER)IS
     BEGIN
        g_daily_fine :=
           GREATEST (LEAST (fine_in, .25), .05);
     END;

     FUNCTION daily_fine RETURN NUMBER IS
     BEGIN
        RETURN g_daily_fine;
     END;

Now it’s impossible to bypass the business rule for the daily fine.

In this particular example, by the way, you will be even better off if you put your maximum and minimum fine information in a table. You could then use the package initialization section to load these limits into package data structures. This way, if (more likely when) the data points change, you won’t have to change the program itself, just some rows and columns in a table.

The only way to change a value is through the set procedure. The values of your data structures are protected; business rules can be enforced without exception.

By hiding the data structure, you also give yourself the freedom to change how that data is defined without affecting all accesses to the data.