7.4 Controlling Output from p

Chapter 7
p: A Powerful Substitute for DBMS_OUTPUT

 

---

The p package offers more flexibility than does DBMS_OUTPUT in determining when output should, in fact, be displayed. With DBMS_OUTPUT, you face an all or nothing scenario. If output has been enabled, you see all information passed to PUT_LINE. If you have not (in SQL*Plus) executed the verbose SET SERVEROUTPUT ON command, nothing appears on the screen.

With p.l, you can match this functionality and then go a bit beyond it as well. The p package provides a toggle to determine whether calls to p.l should generate output. The programs that make up this toggle are:

PROCEDURE turn_on; PROCEDURE turn_off;

If you call turn_off to disable output from p.l, nothing will be displayed -- unless you explicitly request that the information be shown. The last parameter of every overloading of the l procedure is the "show override". If you pass TRUE, the information will always be displayed (assuming that output from DBMS_OUTPUT has been enabled). The default value for the "show override" is FALSE, meaning "do not override."

In the following sequence of calls in SQL*Plus, I manipulate the status of output in the p package to demonstrate how the show override argument can be used.

SQL> exec p.turn_off SQL> exec p.l (SYSDATE); SQL> exec p.l (SYSDATE, show_in => TRUE); *May 12, 1996 22:43:51 SQL> exec p.l (SYSDATE IS NOT NULL, show_in => TRUE); *TRUE SQL> exec p.turn_on SQL> exec p.l(SYSDATE); *May 12, 1996 22:45:47

The p package could, of course, offer much more flexibility even than this variation of all or nothing. Many developers have implemented variations on this package with numeric levels that provide a much finer granularity of choice over which statements will actually display output. Given the nearness of third-party (and Oracle-supplied) debuggers for PL/SQL, however, I exercised self-restraint and focused my efforts in the p package on ease of use and developer productivity.

Special Notes on p

Here are some factors to consider when working with the p package:

• The prefix, line separator, and NULL substitution values can be up to 10 characters in length.

• When you turn_on output from the p package, the DBMS_OUTPUT.ENABLE procedure is called with a maximum size buffer of 1 megabyte.

• Any string that is longer than 80 characters in length will be displayed in a paragraph-wrapped format at a line length of 75 characters.

• The p package will only send information to standard output. If you want to send text to a database table or PL/SQL table or other repository, you might consider using PLVlog or even PLVio if the text has to do with PL/SQL source code.

• The biggest challenge in implementing the p package was to modularize the code inside the package body so that all those overloadings of the l procedure do not result in chaos. I needed to avoid redundant code so that I could easily add to the overloadings as the need arose and even add new functionality to the package's display options (such as paragraph-wrapping long text, a rather recent enhancement). I accomplished this by creating a private module, display_line, which is called by each of the p.l procedures.

• The current set of overloadings of p.l is really quite minimal. You might want to try your hand at enhancing PL/Vision yourself by increasing the variety of datatypes one can pass to the l procedure. What about two numbers or a number and a date? Give it a try!