#

| Chapter 26 Tracing PL/SQL Execution |

## 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.

### 26.4.1 From Idea to Implementation

Sure, this section doesn't provide all of the code behind the interface. However, don't underestimate the importance of getting that interface right. Too many of us spend too little time thinking about how our programs will be used and how the interface to our functionality would be best constructed. Instead, we are eager to dive into the implementation of the programs. Without question, package bodies will be needed for most every package specification you ever write, but if your specification is poorly designed, it will be hard to understand and use -- which means that most likely your code will not be used.

26.3 Free Format Filtering |  | 26.5 Quick-and-Dirty Tracing

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.