Advanced Oracle PL/SQL Programming with Packages

Chapter 7. p: A Powerful Substitute for DBMS_OUTPUT

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