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

To enable this from inside a procedure just add a line saying:
   dbms_output.enable(100000);

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.

Getting Started with DBMS_OUTPUT

DBMS_OUTPUT programs

Table 6-1 shows the DBMS_OUTPUT program names and descriptions.
Table 6-1: DBMS_OUTPUT Programs

Name

Description

Use in SQL?

DISABLE

Disables output from the package; the DBMS_OUTPUT buffer will not be flushed to the screen

Yes

ENABLE

Enables output from the package

Yes

GET_LINE

Gets a single line from the buffer

Yes

GET_LINES

Gets specified number of lines from the buffer and passes them into a PL/SQL table

Yes

NEW_LINE

Inserts an end-of-line mark in the buffer

Yes

PUT

Puts information into the buffer

Yes

PUT_LINE

Puts information into the buffer and appends an end-of-line marker after that data

Yes

DBMS_OUTPUT concepts

Each user has a DBMS_OUTPUT buffer of up to 1,000,000 bytes in size. Write information to this buffer by calling the DBMS_OUTPUT.PUT and DBMS_OUTPUT.PUT_LINE programs. If you are using DBMS_OUTPUT from within SQL*Plus, this information will be displayed automatically when your program terminates. You can (optionally) explicitly retrieve information from the buffer with calls to DBMS_OUTPUT.GET and DBMS_OUTPUT.GET_LINE.

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

The buffer stores three different types of data--VARCHAR2, NUMBER, and DATE--in their internal representations. These types match the overloading available with the PUT and PUT_LINE procedures. Note that DBMS_OUTPUT does not support Boolean data in either its buffer or its overloading of the PUT procedures.

The following anonymous PL/SQL block uses DBMS_OUTPUT to display the name and salary of each employee in department 10:

DECLARE
   CURSOR emp_cur IS
      SELECT ename, sal
        FROM emp
       WHERE deptno = 10
       ORDER BY sal DESC;
BEGIN
   FOR emp_rec IN emp_cur LOOP
      DBMS_OUTPUT.PUT_LINE ('Employee ' || emp_rec.ename || ' earns ' || TO_CHAR (emp_rec.sal) || ' dollars.');
   END LOOP;
END;
/

This program generates the following output when executed in SQL*Plus:

Employee KING earns 5000 dollars.
Employee SCOTT earns 3000 dollars.
Employee JONES earns 2975 dollars.
Employee ADAMS earns 1100 dollars.
Employee JAMES earns 950 dollars.

Enabling output in SQL*Plus

Most developers use DBMS_OUTPUT almost exclusively in the SQL*Plus environment. To enable output from calls to PUT_LINE in SQL*Plus, you will use the SET SERVEROUTPUT command,

SET SERVEROUTPUT ON SIZE 1000000
or:
SET SERVEROUTPUT ON

Each of these calls the DBMS_OUTPUT.ENABLE procedure.

I have found it useful to add SET SERVEROUTPUT ON SIZE 1000000 to my login.sql file, so that the package is automatically enabled whenever I go into SQL*Plus. (I guess that tells you how often I have to debug my code!)

You should also check the Oracle documentation for SQL*Plus to find out about the latest set of options for the SET SERVEROUTPUT command. As of Oracle8, the documentation shows the following syntax for this SET command:

SET SERVEROUT[PUT] {OFF|ON} 
   [SIZE n] [FOR[MAT] {WRA[PPED]| WOR[D_WRAPPED]|TRU[NCATED]}]

In other words, you have these options when you enable DBMS_OUTPUT in SQL*Plus:

SET SERVEROUTPUT OFF
Turns off the display of text from DBMS_OUTPUT.
SET SERVEROUTPUT ON
Turns on the display of text from DBMS_OUTPUT with the default 2000-byte buffer. This is a very small size for the buffer; I recommend that you always specify a size when you call this command.
SET SERVEROUTPUT ON SIZE NNNN
Turns on the display of text from DBMS_OUTPUT with the specified buffer size (maximum of 1,000,000 bytes).
SET SERVEROUTPUT ON FORMAT WRAPPED
(Available in Oracle 7.3 and later only.) Specifies that you want the text displayed by DBMS_OUTPUT wrapped at the SQL*Plus line length. The wrapping occurs regardless of word separation. This will also stop SQL*Plus from stripping leading blanks from your text. You can also specify a SIZE value with this variation.
SET SERVEROUTPUT ON FORMAT WORD_WRAPPED
(Available in Oracle 7.3 and later only.) Specifies that you want the text displayed by DBMS_OUTPUT wrapped at the SQL*Plus line length. This version respects integrity of "words." As a result, lines will be broken in a way that keeps separate tokens intact. This will also stop SQL*Plus from stripping leading blanks from your text. You can also specify a SIZE value with this variation.
SET SERVEROUTPUT ON FORMAT TRUNCATED
(Available in Oracle 7.3 and later only.) Specifies that you want the text displayed by DBMS_OUTPUT to be truncated at the SQL*Plus line length; the rest of the text will not be displayed. This will also stop SQL*Plus from stripping leading blanks from your text. You can also specify a SIZE value with this variation.


Writing to the DBMS_OUTPUT Buffer

You can write information to the buffer with calls to the PUT, NEW_LINE, and PUT_LINE procedures.

The DBMS_OUTPUT.PUT procedure

The PUT procedure puts information into the buffer, but does not append a newline marker into the buffer. Use PUT if you want to place information in the buffer (usually with more than one call to PUT), but not also automatically issue a newline marker. The specification for PUT is overloaded, so that you can pass data in its native format to the package without having to perform conversions,

PROCEDURE DBMS_OUTPUT.PUT (A VARCHAR2);
PROCEDURE DBMS_OUTPUT.PUT (A NUMBER);
PROCEDURE DBMS_OUTPUT.PUT (A DATE);

where A is the data being passed.

Example

In the following example, three simultaneous calls to PUT place the employee name, department ID number, and hire date into a single line in the DBMS_OUTPUT buffer:

DBMS_OUTPUT.PUT (:employee.lname || ', ' || :employee.fname);
DBMS_OUTPUT.PUT (:employee.department_id);
DBMS_OUTPUT.PUT (:employee.hiredate);

If you follow these PUT calls with a NEW_LINE call, that information can then be retrieved with a single call to GET_LINE.

The DBMS_OUTPUT.PUT_LINE procedure

The PUT_LINE procedure puts information into the buffer and then appends a newline marker into the buffer. The specification for PUT_LINE is overloaded, so that you can pass data in its native format to the package without having to perform conversions:

PROCEDURE DBMS_OUTPUT.PUT_LINE (A VARCHAR2);
PROCEDURE DBMS_OUTPUT.PUT_LINE (A NUMBER);
PROCEDURE DBMS_OUTPUT.PUT_LINE (A DATE);

The PUT_LINE procedure is the one most commonly used in SQL*Plus to debug PL/SQL programs. When you use PUT_LINE in these situations, you do not need to call GET_LINE to extract the information from the buffer. Instead, SQL*Plus will automatically dump out the DBMS_OUTPUT buffer when your PL/SQL block finishes executing. (You will not see any output until the program ends.)

Of course, you can also call DBMS_OUTPUT programs directly from the SQL*Plus command prompt, and not from inside a PL/SQL block, as shown in the following example.

Example

Suppose that you execute the following three statements in SQL*Plus:

SQL> exec DBMS_OUTPUT.PUT ('I am');
SQL> exec DBMS_OUTPUT.PUT (' writing ');
SQL> exec DBMS_OUTPUT.PUT ('a ');

You will not see anything, because PUT will place the information in the buffer, but will not append the newline marker. When you issue this next PUT_LINE command,

SQL> exec DBMS_OUTPUT.PUT_LINE ('book!');

you will then see the following output:

I am writing a book!

All of the information added to the buffer with the calls to PUT waited patiently to be flushed out with the call to PUT_LINE. This is the behavior you will see when you execute individual calls at the SQL*Plus command prompt to the put programs.

If you place these same commands in a PL/SQL block,

BEGIN
   DBMS_OUTPUT.PUT ('I am');
   DBMS_OUTPUT.PUT (' writing ');
   DBMS_OUTPUT.PUT ('a ');
   DBMS_OUTPUT.PUT_LINE ('book');
END;
/

the output from this script will be exactly the same as that generated by this single call:

SQL> exec DBMS_OUTPUT.PUT_LINE ('I am writing a book!');

The DBMS_OUTPUT.NEW_LINE procedure

The NEW_LINE procedure inserts an end-of-line marker in the buffer. Use NEW_LINE after one or more calls to PUT in order to terminate those entries in the buffer with a newline marker. Here's the specification for NEW_LINE:

PROCEDURE DBMS_OUTPUT.NEW_LINE;

Retrieving Data from the DBMS_OUTPUT Buffer

You can use the GET_LINE and GET_LINES procedures to extract information from the DBMS_OUTPUT buffer. If you are using DBMS_OUTPUT from within SQL*Plus, however, you will never need to call either of these procedures. Instead, SQL*Plus will automatically extract the information and display it on the screen for you.

The DBMS_OUTPUT.GET_LINE procedure

The GET_LINE procedure retrieves one line of information from the buffer. Here's the specification for the procedure:

PROCEDURE DBMS_OUTPUT.GET_LINE 
   (line OUT VARCHAR2, 
    status OUT INTEGER);

The parameters are summarized in the following table.








Parameter

Description

line

Retrieved line of text

status

GET request status

The line can have up to 255 bytes in it, which is not very long. If GET_LINE completes successfully, then status is set to 0. Otherwise, GET_LINE returns a status of 1.

Notice that even though the PUT and PUT_LINE procedures allow you to place information into the buffer in their native representations (dates as dates, numbers and numbers, and so forth), GET_LINE always retrieves the information into a character string. The information returned by GET_LINE is everything in the buffer up to the next newline character. This information might be the data from a single PUT_LINE or from multiple calls to PUT.

Example

The following call to GET_LINE extracts the next line of information into a local PL/SQL variable:

FUNCTION get_next_line RETURN VARCHAR2 
IS
   return_value VARCHAR2(255);
   get_status INTEGER;
BEGIN
   DBMS_OUTPUT.GET_LINE (return_value, get_status);
   IF get_status = 0 THEN
      RETURN return_value;
   ELSE
      RETURN NULL;
   END IF;
END;

The DBMS_OUTPUT.GET_LINES procedure

The GET_LINES procedure retrieves multiple lines from the buffer with one call. It reads the buffer into a PL/SQL string table. Here's the specification for the procedure:

PROCEDURE DBMS_OUTPUT.GET_LINES 
   (lines OUT DBMS_OUTPUT.CHARARR, 
    numlines IN OUT INTEGER);

The parameters for this procedure are summarized in the following table.


Parameter

Description

lines

PL/SQL array where retrieved lines are placed

numlines

Number of individual lines retrieved from the buffer and placed into the array

The lines parameter is a PL/SQL table TYPE declared in the specification of the package. It is described at the beginning of this chapter.

The values retrieved by GET_LINES are placed in the first numlines rows in the table, starting from row one. As indicated in the PL/SQL table structure, each line (row in the table) may contain up to 255 bytes.

Notice that numlines is an IN OUT parameter. The IN aspect of the parameter specifies the number of lines to retrieve. Once GET_LINES is done retrieving data, however, it sets numlines to the number of lines actually placed in the table. If you ask for ten rows and there are only six in the buffer, then you need to know that only the first six rows of the table are defined.

Notice also that even though the PUT and PUT_LINE procedures allow you to place information into the buffer in their native representations (dates as dates, numbers and numbers, and so forth), GET_LINES always retrieves the information into a character string. The information in each line returned by GET_LINES is everything in the buffer up to the next newline character. This information might be the data from a single PUT_LINE or from multiple calls to PUT.

While GET_LINES is provided with the DBMS_OUTPUT package, it is not needed to retrieve information from the DBMS_OUTPUT buffer--at least when used inside SQL*Plus. In this interactive query tool, you simply execute calls to PUT_LINE, and when the PL/SQL block terminates, SQL*Plus will automatically dump the buffer to the screen.

Example

The following script demonstrates both the kind of code you would write when using the GET_LINES procedure, and also the way in which the PL/SQL table is filled:

/* Filename on companion disk: getlines.tst */
DECLARE
  output_table DBMS_OUTPUT.CHARARR;  /* output_buf_tab */
  a_line VARCHAR2(10) := RPAD('*',10,'*');
  status INTEGER;
  max_lines CONSTANT NUMBER := 15;
BEGIN
   output_table (0) := 'ABC';
   output_table (12) := 'DEF';
   
   /* Output 10 lines */
   FOR linenum IN 1..10 LOOP
      DBMS_OUTPUT.PUT_LINE (a_line ||  TO_CHAR (linenum);
   END LOOP;
   /* retrieve 15 lines, status will receive the line count */
   status := max_lines;
   DBMS_OUTPUT.GET_LINES ( output_table, status);
   DBMS_OUTPUT.PUT_LINE ('lines retrieved= ' || status));
  
   FOR linenum in 0..max_lines LOOP
       BEGIN
          DBMS_OUTPUT.PUT_LINE (linenum || ':' || NVL (output_table(linenum),'<null>') );
       EXCEPTION
          WHEN OTHERS THEN
             DBMS_OUTPUT.PUT_LINE (linenum || ':' || sqlerrm );
       END;
    END LOOP;
EXCEPTION
   WHEN OTHERS 
   THEN
       DBMS_OUTPUT.PUT_LINE ('Exception, status=' || status);
       DBMS_OUTPUT.PUT_LINE (SQLERRM );
END;
/

Here is the output from the execution of this script:

lines retrieved= 10
0:ORA-01403: no data found
1:**********1
2:**********2
3:**********3
4:**********4
5:**********5
6:**********6
7:**********7
8:**********8
9:**********9
10:**********10
11:<null>
12:ORA-01403: no data found
13:ORA-01403: no data found
14:ORA-01403: no data found
15:ORA-01403: no data found

You can therefore deduce the following rules:

  1. The PL/SQL table is filled starting with row 1.

  2. If DBMS_OUTPUT.GET_LINES finds N lines of data to pass to the PL/SQL table, it sets row N+1 in that table to NULL.
  3. All other rows in the PL/SQL table are set to "undefined." In other words, any other rows that might have been defined before the call to GET_LINES are deleted.

Tips on Using DBMS_OUTPUT

As noted at the beginning of the chapter, DBMS_OUTPUT comes with several handicaps. The best way to overcome these handicaps is to create your own layer of code over the built-in package. 

Regardless of the use of an encapsulation package, you should keep the following complications in mind as you work with DBMS_OUTPUT:

  1. If your program raises an unhandled exception, you may not see any executed output from PUT_LINE, even if you enabled the package for output .

    This can happen because the DBMS_OUTPUT buffer will not be flushed until it is full or until the current PL/SQL block completes its execution. If a raised exception never gets handled, the buffer will not be flushed. As a result, calls to the DBMS_OUTPUT.PUT_LINE module might never show their data. So if you are working with DBMS_OUTPUT.PUT_LINE and are frustrated because you are not seeing the output you would expect, make sure that you have:

    1. Enabled output from the package by calling SET SERVEROUTPUT ON in SQL*Plus.

    2. Placed an exception section with a WHEN OTHERS handler in the outer block of your code (usually some sort of test script) so that your output can be flushed to your terminal by SQL*Plus.
  2. When package state has been reinitialized in your session, DBMS_OUTPUT is reset to "not enabled."

    Packages can be reset to their initial state with a call to DBMS_SESSION.RESET_PACKAGE. (See Chapter 11, Managing Session Information, for more information about this program.) You might call this procedure yourself, but that is unlikely. A more common scenario for resetting package states is when an error is raised in your session that causes packages to be reset to their initial state. Here is the error for which you need to beware:

    ERROR at line 1:

            ORA-04068: existing state of packages has been discarded
            ORA-04061: existing state of package "PKG.PROC" has been invalidated
            ORA-04065: not executed, altered or dropped package "PKG.PROC"
            ORA-06508: PL/SQL: could not find program unit being called

If you get this error and simply continue with your testing, you may be surprised to find that you are not getting any output. If you remember that DBMS_OUTPUT relies on package variables for its settings, this makes perfect sense. So when you get the preceding error, you should immediately "re-enable" DBMS_OUTPUT with a command such as the following:

SQL> set serveroutput on size 1000000 format wrapped

I usually just re-execute my login.sql script, since I may be initializing several different packages:

SQL> @login.sql

When will you get this error? I have found that it occurs when I have multiple sessions connected to Oracle. Suppose that I am testing program A in session USER1. I run it and find a bug. I fix the bug and recompile program A in session USER2 (the owner of the code). When I try to execute program A from session USER1 again, it raises the ORA-04068 error.

If you do encounter this error, don't panic. Just reset your package variables and run the program again. It will now work fine; the error is simply the result of a quirk in Oracle's automatic recompilation feature.