Chapter 7 - p: A Powerful Substitute for DBMS_OUTPUT
| Chapter 7 |
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.
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). |
