#

| Chapter 7 p: A Powerful Substitute for DBMS_OUTPUT |

## 7.4 Controlling Output from p

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.

7.3 The Output Prefix |  | 8. PLVtab: Easy Access to PL/SQL Tables

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