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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.