O'Reilly logo

Oracle Built-in Packages by John Beresniewicz, Charles Dye, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 6. Generating Output from PL/SQL Programs

The built-in packages offer a number of ways to generate output from within your PL/SQL program. While updating a database table is, of course, a form of “output” from PL/SQL, this chapter shows you how to use two packages that explicitly generate output. UTL_FILE reads and writes information in server-side files, and DBMS_OUTPUT displays information to your screen.

DBMS_OUTPUT: Displaying Output

DBMS_OUTPUT provides a mechanism for displaying information from your PL/SQL program on your screen (your session’s output device, to be more specific). As such, it serves as just about the only immediately accessible (meaning “free with PL/SQL”) means of debugging your PL/SQL stored code.[8] It is certainly your “lowest common denominator” debugger, similar to the used-and-abused MESSAGE built-in of Oracle Forms. DBMS_OUTPUT is also the package you are most likely to use to generate reports from PL/SQL scripts run in SQL*Plus.

Of all the built-in packages, the DBMS_OUTPUT package (and its PUT_LINE procedure, in particular) is likely to be the one you will find yourself using most frequently. You may therefore find it strange that I never call DBMS_OUTPUT.PUT_LINE. I find the design and functionality of DBMS_OUTPUT to be substandard and very frustrating.

In fact, I recommend that you never use this package—at least, not directly. You should instead encapsulate calls to DBMS_OUTPUT (and the PUT_LINE procedure, in particular) inside a package ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required