26.4 Structured Interface Filtering

You get into a lot more code with this approach. Let's look at implementing an interface to support numeric levels. You will be able to ask to see trace information for one level, or a range of levels, or even a list of distinct levels. Because of the complexity and the need to maintain consistent formats of information, you would no longer perform your own direct inserts into the tracetab_activate table. Instead, you call a procedure which converts your request into the format necessary to store in the table. Here are some of those procedures:

PACKAGE trace
IS
   /* Just show the programs for this filtering. */
   PROCEDURE filter_by
      (username_in IN VARCHAR2, level_in IN INTEGER);

   PROCEDURE filter_by
      (username_in IN VARCHAR2, min_in IN INTEGER, max_in IN INTEGER);

   PROCEDURE filter_by
      (username_in IN VARCHAR2, list_in IN VARCHAR2);

END trace;

Notice that all three of these filtering programs have the same name. These are overloaded procedures; overloading is discussed in the section "Module Overloading" in Chapter 15, Procedures and Functions .

Here are some examples of these programs in use:

1. Request activation of trace on the SFEUERST account for level 16:

SQL> exec trace.filter_by ('sfeuerst', 16);

2. Request activation of trace on the LELLISON account for levels 16-25:

SQL> exec trace.filter_by ('lellsion', 16, 25);

3. Request activation of trace on the WGATES account for levels 2, 6, 10, and 55:

SQL> exec trace.filter_by ('wgates', '2,6,10,55');

Gee, that looks easy enough to use! Of course, the devil is in the details. In all three cases, the procedures must convert the levels provided into a string with a consistent format so that the trace.startup program (and others) can query the tracetab_activate table and properly interpret the request.