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 (fine_in IN NUMBER)
IS
BEGIN
   IF tracing
   THEN
      watch.action ('set_daily_fine', fine_in);
   END IF;
   
   g_daily_fine := 
      GREATEST (LEAST (fine_in, .25), .05);
END set_daily_fine;

Rather than call DBMS_OUTPUT.PUT_LINE or even my enhanced pl procedure, notice that I call watch.action. The watch package is a more robust tracing mechanism. Among other features, it allows you to direct output to a database pipe, bypassing the buffer limitations of DBMS_OUTPUT.

Now, when I want to watch what is happening inside my overdue package, I simply turn on trace for my session before I run my application code:

SQL> exec overdue_pkg.trc
SQL> exec library_test

Benefits

This “read-only” window into a package can help you ensure that you’re using the package properly or allow you to confirm that the data you’re passing into the package is correct. Such windows increase your confidence in the package and allow you to use it more effectively. The on-off switch for the window is based on the “get and set” technique discussed in [DAT-15: Expose package globals using “get and set” modules.].

Windows are easy to add to your package after you have written the base code. Just add an on-off switch and put an IF statement inside the appropriate module.

Challenges

If you add trace calls, add them comprehensively, taking into account the perspective and needs of the user. Looking through a smoky window may be more confusing than not being able to see at all.

It’s worth coming up with a plan for where and when you will insert trace calls in your code. Take into account possible impact on performance and readability. You may also find yourself adding trace logic iteratively as you work on different sections of code, in order to watch several computations or logic paths.

Resources