26.3 Free Format Filtering

Chapter 26
Tracing PL/SQL Execution

 

---

With the free format approach, you could simply add a column onto the tracetab_activate table which would contain the wildcard string:

CREATE TABLE tracetab_activate    (username VARCHAR2(60),     action VARCHAR2(20),     matchto VARCHAR2(100));

Then when you insert into the table to "turn on" logging of trace information, you provide the string:

INSERT INTO tracetab_activate VALUES ('SAM_I_AM', 'Y', '%INVOICE%');

In this case, I am looking for trace messages which contain the word INVOICE. I would then modify the activated function shown earlier in the article to a procedure which returns a TRUE/FALSE value, to indicate whether or not the trace is activated for this user, as well as the match string. Here is the header for such a procedure:

PACKAGE trace IS    . . .    PROCEDURE check_activation       (turnon_out OUT BOOLEAN, match_out OUT VARCHAR2);    . . . END trace;

My trace.startup procedure would call the check_activation routine as follows:

PROCEDURE startup IS    v_turnon BOOLEAN;    v_match VARCHAR2(100); BEGIN    check_activation (v_turnon, v_match);    IF v_turnon    THEN       log (v_match);    ELSE       nolog;    END IF;    . . . END:

The log procedure accepts the match string, setting flags internal to PLVlog so that the trace message is checked against the match string and is passed on to the log if a match is found.