Oracle PL/SQL Best Practices

Name

DAT-15: Expose package globals using “get and set” modules.

Synopsis

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

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.

Example

I’ve created a package to calculate overdue fines. 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:

CREATE OR REPLACE PACKAGE overdue_pkg
IS
   g_daily_fine NUMBER := .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;

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

As you can see, 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 ...