7. p: A Powerful Substitute for DBMS_OUTPUT

Contents:
Using the l Procedure
The Line Separator
The Output Prefix
Controlling Output from p

The p (PL/Vision Put) package is one of the first and simplest packages I ever built. It is also one of my favorites. The concept is clear, the payback immediate and everlasting. It also demonstrates some of the key advantages of PL/SQL packages in general.

The p package offers a powerful, flexible substitute for the DBMS_OUTPUT.PUT_LINE builtin package (see sidebar for a quick review of DBMS_OUTPUT). This is the l procedure. Generally, you will use the l procedure of the p package in place of DBMS_OUTPUT.PUT_LINE to display output from within a PL/SQL program. The p package improves your development productivity by minimizing keystrokes (as I described in Chapter 1, PL/SQL Packages, I grew to detest those 20 characters and sought out names for the package and procedure that would involve the smallest amount of typing possible), but its advantages go beyond this superficial benefit .

The builtin DBMS_OUTPUT.PUT_LINE procedure, particularly as it is supported within the SQL*Plus environment, has the following complications:

If you pass it a string that is longer than 255 bytes, the PL/SQL runtime engine raises the VALUE_ERROR exception.
If you try to display a NULL value, PUT_LINE simply ignores your request. Not even a blank line is displayed.
All leading blanks are trimmed from the string when displayed.[1]
[1] In SQL*Plus 3.3, you can issue the command set serveroutput on format wrapped, and leading blanks are preserved and long lines are wrapped to the length specified in SQL*Plus. However, you still can't display more than 255 characters. [undocumented feature reported by Laurence Pit]
PUT_LINE's overloading is quite limited. It cannot display a Boolean value, nor can it handle combinations of data.

I created the p package to compensate for these deficiencies -- and then I put it to use. In fact, you will find only one package in PL/Vision in which DBMS_OUTPUT.PUT_LINE is called: the p package. I have been very careful to always use my own substitution program for this builtin, to make sure that the robust features of p.l are leveraged throughout the library.

The following sections of this chapter show you how to use p.l to display information. They also show you how to modify the behavior of p.l as follows:

Set the line prefix. This prefix allows you to preserve leading spaces.
Set the line separator. This character gives you a way to preserve white space (blank lines) in your source.
Control p.l output. The show override argument in the p.l procedure gives you some added flexibility over when p.l will actually show you something.

7.1 Using the l Procedure

The p.l procedure is a pleasure to use. When you call p.l instead of the DBMS_OUTPUT.PUT_LINE procedure, you will never have to worry about raising the VALUE_ERROR exception. You can display values of up to 32,767 bytes! You can pass many different kinds of data to the l procedure and it will figure out what to do and how to best display the information.

What, you worry? No, you let the package do the worrying for you. You get to concentrate on building your application.

You use p.l just as you would its builtin cousin, except that the p package offers a much wider overloading for different types and combinations of types of data. You pass it one or more values for display purposes. You can also use the final argument of the p.l procedure to control when output should be displayed (see Section 7.4, "Controlling Output from p ").

Here are the headers for the version of p.l that display a number and a string-date combination, respectively.

PROCEDURE l (number_in IN NUMBER, show_in IN BOOLEAN := FALSE);

PROCEDURE l 
   (char_in IN VARCHAR2, date_in IN DATE,
    mask_in IN VARCHAR2 := PLV.datemask,
    show_in IN BOOLEAN := FALSE);

To view the salary of an employee, you simply execute:

p.l (emp_rec.sal);

To view the employee name and hire date, you execute:

p.l (emp_rec.ename, emp_rec.hiredate)

and you will see this data in this format:

JONES: May 12, 1981 22:45:47

To get the same information using the default functionality of DBMS_OUTPUT, you would have to enter something as ugly and time-consuming as this:

DBMS_OUTPUT.PUT_LINE (emp_rec.ename || ': ' || 
   TO_CHAR (emp_rec.hiredate, 'FMMonth DD, YYYY HH:MI:SS'))

Which would you rather type? That should give you a good sense of the potential productivity gains available through p![2]

[2] Did you ever notice how old the data in the emp table is? Oracle Corporation should update that demonstration table to reflect corporate growth and increased salaries...but I guess they have to worry about backward compatibility of demonstration scripts!

7.1.1 Valid Data Combinations for p.l

Table 7.1 shows the different types of data that can be passed to the p.l procedure.

See the p package specification (or the table in Chapter 5, PL/Vision Package Specifications) for the headers of all the corresponding versions of the l procedure.

Table 7.1: Valid Data Combinations for p.l
Data Combinations	Resulting Value
VARCHAR2	The string as supplied by the user.
DATE	The date converted to a string, using the specified date mask. The default date mask is provided by `PLV.datemask` -- a PL/Vision-wide setting.
NUMBER	The number converted to a string using the default format mask.
BOOLEAN	The string "TRUE" if the Boolean expression evaluates to TRUE, "FALSE" if FALSE, and the NULL substitution value if NULL.
VARCHAR2, DATE	The string concatenated to a colon, concatenated to the date (converted to a string as explained above).
VARCHAR2, NUMBER	The string concatenated to a colon, concatenated to the number (converted to a string as explained above).
VARCHAR2, BOOLEAN	The string concatenated to a colon, concatenated to the Boolean (converted to a string as explained above).

7.1.2 Displaying Dates

When you display a date using p.l, it uses the string returned by the PLV.datemask function as the format mask. The default value of the format mask is:

The DBMS_OUTPUT package allows you to display information to your session's output device from within your PL/SQL program. As such, it serves as just about the only easily accessible means of debugging your PL/SQL Version 2 programs. DBMS_OUTPUT is also the package you will use to generate reports from PL/SQL scripts run in SQL*Plus.

Theoretically, you write information to the DBMS_OUTPUT buffer with calls to PUT_LINE and PUT and then extract that information for display with the GET_LINE program. In reality (in SQL*Plus, anyway), you simply call the PUT_LINE program from within your PL/SQL program and when your program finishes executing, all the text "put" to the buffer is displayed on your screen. The following SQL*Plus session gives you an idea of what you must type:

SQL> exec DBMS_OUTPUT.PUT_LINE ('this is great!');
this is great

The size of the DBMS_OUTPUT buffer can be set to a size between 2,000 bytes (the default) and 1,000,000 bytes with the ENABLE procedure. If you do not ENABLE the package, then no information will be displayed or will be retrievable from the buffer.

When using DBMS_OUTPUT in SQL*Plus, you can use the SET command to enable output from DBMS_OUTPUT and also set the size of the buffer. To enable output, you must issue this command:

SQL> set serveroutput on

To set the buffer size to a value other than 2,000, add the size clause as follows:

SQL> set serveroutput on size 1000000

I recommend that you put the SET SERVEROUTPUT command in your login.sql script so your session is automatically enabled for output. Remember, however, that every time you reconnect inside SQL*Plus, all of your package variables are reinitialized. So if you issue a CONNECT command in SQL*Plus, you will need to reenable DBMS_OUTPUT. The script ssoo.sql (on the disk) does this for you with a minimum of fuss. To enable output and set the buffer to its maximize size (1 megabyte), simply type:

SQL> @ssoo

See Chapter 15 of Oracle PL/SQL Programming, for more details on DBMS_OUTPUT.

'FMMonth DD, YYYY FMHH24:MI:SS'

If you would like to change the format used to display dates, you can either specify a new format when you call p.l or you can change the default mask maintained by the PLV package.

To specify a different format in the call to p.l, simply include the mask string after the date argument. Here, for example, is the header for the version of p.l that displays a date:

PROCEDURE l 
   (date_in IN DATE,
    mask_in IN VARCHAR2 := PLV.datemask,
    show_in IN BOOLEAN := FALSE);

So to display the name of an employee and the month/year she was hired, I can use:

p.l (emp_rec.ename, emp_rec.hiredate, 'Month YYYY');

Alternatively, I can set the default format for any date displayed in PLV with this call:

PLV.set_datemask ('Month YYYY');

and then the call to p.l could be simplified to:

p.l (emp_rec.ename, emp_rec.hiredate);


6.6 The Predefined Constants		7.2 The Line Separator

7. p: A Powerful Substitute for DBMS_OUTPUT

7.1 Using the l Procedure

7.1.1 Valid Data Combinations for p.l

7.1.2 Displaying Dates

The DBMS_OUTPUT Package