Advanced Oracle PL/SQL Programming with Packages

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