Oracle PL/SQL Best Practices

Name

PKG-05: Build trace “windows” into your packages using standardized programs.

Synopsis

On the one hand, it’s very helpful to use packages to hide complexity. On the other hand, it’s often the case that users can be greatly aided by being able to look inside packages and watch what’s happening.

You can easily build a “read-only” window into a package. Users can open the window when and if he wants to, watch what the package is doing (or at least what the author of the package claims the package is doing), and then shut the window when that information isn’t needed.

To build a window, you will need to:

• Add tracing code inside the package body.

• Supply an on-off switch in the specification so that users can open and close the window.

Example

My overdue fines package allows a user to set the daily fine rate, as shown by this partial package body:

CREATE OR REPLACE PACKAGE BODY overdue_pkg
IS
   g_daily_fine NUMBER := .10;

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

I now want to add a trace to this package so that a user of overdue_pkg can see what the daily fine is being set to when her application runs. First, I add an on-off switch to the package specification and body:

CREATE OR REPLACE PACKAGE overdue_pkg
IS
   ... other elements in package
      
   PROCEDURE trc;
   PROCEDURE notrc;
   FUNCTION tracing RETURN BOOLEAN;
END overdue_pkg;

Then, I add my trace to the set_daily_ fine procedure:

PROCEDURE set_daily_fine ...