4 DBMS_OUTPUT

The DBMS_OUTPUT package enables you to send messages from stored procedures and packages. The package is especially useful for displaying PL/SQL debugging information.

This chapter contains the following topics:

Using DBMS_OUTPUT

This section contains topics which relate to using the DBMS_OUTPUT package.

Overview

The PUT procedure and PUT_LINE procedure in this package enable you to place information in a buffer that can be read by another procedure or package. In a separate PL/SQL procedure or anonymous block, you can display the buffered information by calling the GET_LINE procedure and GET_LINES procedure.

If the package is disabled, all calls to subprograms are ignored. In this way, you can design your application so that subprograms are available only when a client can process the information.

Operational notes

  • If you do not call GET_LINE, or if you do not display the messages on your screen in ttIsql, the buffered messages are ignored.

  • The ttIsql utility calls GET_LINES after issuing a SQL statement or anonymous PL/SQL calls.

  • Typing SET SERVEROUTPUT ON in ttIsql has the same effect as the following:

    DBMS_OUTPUT.ENABLE (buffer_size => NULL);

    There is no limit on the output.

  • You should generally avoid having application code invoke either the DISABLE procedure or ENABLE procedure because this could subvert the attempt by an external tool like ttIsql to control whether to display output.

Note:

Messages sent using DBMS_OUTPUT are not actually sent until the sending subprogram completes. There is no mechanism to flush output during the execution of a procedure.

Rules and limits

  • The maximum line size is 32767 bytes.

  • The default buffer size is 20000 bytes. The minimum size is 2000 bytes and the maximum is unlimited.

Exceptions

DBMS_OUTPUT subprograms raise the application error ORA-20000, and the output procedures can return the following errors:

Table 4-1 DBMS_OUTPUT exceptions

Exception Description

ORU-10027

Buffer overflow

ORU-10028

Line length overflow

Examples

The DBMS_OUTPUT package is commonly used to debug stored procedures or functions.

This function queries the employees table of the HR schema and returns the total salary for a specified department. The function includes calls to the PUT_LINE procedure:

CREATE OR REPLACE FUNCTION dept_salary (dnum NUMBER) RETURN NUMBER IS
   CURSOR emp_cursor IS
   select salary, commission_pct from employees where department_id = dnum;
   total_wages NUMBER(11, 2) := 0;
   counter NUMBER(10) := 1;
BEGIN
   FOR emp_record IN emp_cursor LOOP
       emp_record.commission_pct := NVL(emp_record.commission_pct, 0);
       total_wages := total_wages + emp_record.salary
                   + emp_record.commission_pct;
       DBMS_OUTPUT.PUT_LINE('Loop number = ' || counter ||
          '; Wages = '|| TO_CHAR(total_wages)); /* Debug line */
       counter := counter + 1; /* Increment debug counter */
   END LOOP;
   /* Debug line */
   DBMS_OUTPUT.PUT_LINE('Total wages = ' ||
   TO_CHAR(total_wages));
   RETURN total_wages;
END;

Assume the user executes the following statements in ttIsql:

Command> SET SERVEROUTPUT ON
Command> VARIABLE salary NUMBER;
Command> EXECUTE :salary := dept_salary(20);

The user would then see output such as the following:

Loop number = 1; Wages = 13000
Loop number = 2; Wages = 19000
Total wages = 19000 

PL/SQL procedure successfully executed.

Data structures

The DBMS_OUTPUT package declares two table types for use with the GET_LINES procedure.

Notes:

  • The PLS_INTEGER and BINARY_INTEGER data types are identical. This document uses BINARY_INTEGER to indicate data types in reference information (such as for table types, record types, subprogram parameters, or subprogram return values), but may use either in discussion and examples.

  • The INTEGER and NUMBER(38) data types are also identical. This document uses INTEGER throughout.

Table types

CHARARR table type

DBMSOUTPUT_LINESARRAY table type

CHARARR table type

This package type is to be used with the GET_LINES procedure to obtain text submitted through the PUT procedure and PUT_LINE procedure.

Syntax

TYPE CHARARR IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;

DBMSOUTPUT_LINESARRAY table type

This package type is to be used with the GET_LINES procedure to obtain text submitted through the PUT procedure and PUT_LINE procedure.

Syntax

TYPE DBMSOUTPUT_LINESARRAY IS
     VARRAY(2147483647) OF VARCHAR2(32767);

Summary of DBMS_OUTPUT subprograms

Table 4-2 DBMS_OUTPUT package subprograms

Subprogram Description

DISABLE procedure

Disables message output.

ENABLE procedure

Enables message output.

GET_LINE procedure

Retrieves one line from buffer.

GET_LINES procedure

Retrieves an array of lines from buffer.

NEW_LINE procedure

Terminates a line created with PUT.

PUT procedure

Places a line in the buffer.

PUT_LINE procedure

Places partial line in buffer.

DISABLE procedure

This procedure disables calls to PUT, PUT_LINE, NEW_LINE, GET_LINE, and GET_LINES, and purges the buffer of any remaining information.

As with the ENABLE procedure, you do not need to call this procedure if you are using the SET SERVEROUTPUT ON setting from ttIsql.

Syntax

DBMS_OUTPUT.DISABLE;

ENABLE procedure

This procedure enables calls to PUT, PUT_LINE, NEW_LINE, GET_LINE, and GET_LINES. Calls to these procedures are ignored if the DBMS_OUTPUT package is not activated.

Syntax

DBMS_OUTPUT.ENABLE (
   buffer_size IN INTEGER DEFAULT 20000);

Parameters

Table 4-3 ENABLE procedure parameters

Parameter Description

buffer_size

Upper limit, in bytes, for the amount of buffered information

Setting buffer_size to NULL specifies that there should be no limit.

Usage notes

  • It is not necessary to call this procedure when you use SET SERVEROUTPUT ON from ttIsql. It is called automatically (with NULL value for buffer_size in the current release).

  • If there are multiple calls to ENABLE, then buffer_size is the last of the values specified. The maximum size is 1,000,000 and the minimum is 2000 when the user specifies buffer_size (NOT NULL).

  • NULL is expected to be the usual choice. The default is 20000 for backward compatibility with earlier database versions that did not support unlimited buffering.

GET_LINE procedure

This procedure retrieves a single line of buffered information.

Syntax

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

Parameters

Table 4-4 GET_LINE procedure parameters

Parameter Description

line

A single line of buffered information, excluding a final newline character

You should declare this parameter as VARCHAR2(32767) to avoid the risk of "ORA-06502: PL/SQL: numeric or value error: character string buffer too small".

status

Call status

If the call completes successfully, then the status returns as 0. If there are no more lines in the buffer, then the status is 1.

Usage notes

  • You can choose to retrieve from the buffer a single line or an array of lines. Call GET_LINE to retrieve a single line of buffered information. To reduce the number of calls to the server, call GET_LINES to retrieve an array of lines from the buffer.

  • You can choose to automatically display this information if you are using ttIsql by using the special SET SERVEROUTPUT ON command.

  • After calling GET_LINE or GET_LINES, any lines not retrieved before the next call to PUT, PUT_LINE, or NEW_LINE are discarded to avoid confusing them with the next message.

GET_LINES procedure

This procedure retrieves an array of lines from the buffer.

Syntax

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

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

Parameters

Table 4-5 GET_LINES procedure parameters

Parameter Description

lines

Array of lines of buffered information

The maximum length of each line in the array is 32767 bytes. It is recommended that you use the varray overload version in a 3GL host program to execute the procedure from a PL/SQL anonymous block.

numlines

Number of lines you want to retrieve from the buffer

After retrieving the specified number of lines, the procedure returns the number of lines actually retrieved. If this number is less than the number of lines requested, then there are no more lines in the buffer.

Usage notes

  • You can choose to retrieve from the buffer a single line or an array of lines. Call GET_LINE to retrieve a single line of buffered information. To reduce the number of trips to the server, call GET_LINES to retrieve an array of lines from the buffer.

  • You can choose to automatically display this information if you are using ttIsql by using the special SET SERVEROUTPUT ON command.

  • After GET_LINE or GET_LINES is called, any lines not retrieved before the next call to PUT, PUT_LINE, or NEW_LINE are discarded to avoid confusing them with the next message.

NEW_LINE procedure

This procedure puts an end-of-line marker. The GET_LINE procedure and the GET_LINES procedure return "lines" as delimited by "newlines". Every call to the PUT_LINE procedure or to NEW_LINE generates a line that is returned by GET_LINE or GET_LINES.

Syntax

DBMS_OUTPUT.NEW_LINE;

PUT procedure

This procedure places a partial line in the buffer.

Note:

The PUT version that takes a NUMBER input is obsolete. It is supported for legacy reasons only.

Syntax

DBMS_OUTPUT.PUT (
    a IN VARCHAR2);

Parameters

Table 4-6 PUT procedure parameters

Parameter Description

a

Item to buffer

Usage notes

  • You can build a line of information piece by piece by making multiple calls to PUT, or place an entire line of information into the buffer by calling PUT_LINE.

  • When you call PUT_LINE, the item you specify is automatically followed by an end-of-line marker. If you make calls to PUT to build a line, you must add your own end-of-line marker by calling NEW_LINE. GET_LINE and GET_LINES do not return lines that have not been terminated with a newline character.

  • If your lines exceed the line limit, you receive an error message.

  • Output that you create using PUT or PUT_LINE is buffered. The output cannot be retrieved until the PL/SQL program unit from which it was buffered returns to its caller.

Exceptions

Table 4-7 PUT procedure exceptions

Exception Description

ORA-20000, ORU-10027

Buffer overflow, according to the buffer_size limit specified in the ENABLE procedure call

ORA-20000, ORU-10028

Line length overflow, limit of 32767 bytes for each line

PUT_LINE procedure

This procedure places a line in the buffer.

Note:

The PUT_LINE version that takes a NUMBER input is obsolete. It is supported for legacy reasons only.

Syntax

DBMS_OUTPUT.PUT_LINE (
   a IN VARCHAR2);

Parameters

Table 4-8 PUT_LINE procedure parameters

Parameter Description

a

Item to buffer

Usage notes

  • You can build a line of information piece by piece by making multiple calls to PUT, or place an entire line of information into the buffer by calling PUT_LINE.

  • When you call PUT_LINE, the item you specify is automatically followed by an end-of-line marker. If you make calls to PUT to build a line, then you must add your own end-of-line marker by calling NEW_LINE. GET_LINE and GET_LINES do not return lines that have not been terminated with a newline character.

  • If your lines exceeds the line limit, you receive an error message.

  • Output that you create using PUT or PUT_LINE is buffered. The output cannot be retrieved until the PL/SQL program unit from which it was buffered returns to its caller.

Exceptions

Table 4-9 PUT_LINE procedure exceptions

Exception Description

ORA-20000, ORU-10027

Buffer overflow, according to the buffer_size limit specified in the ENABLE procedure call

ORA-20000, ORU-10028

Line length overflow, limit of 32767 bytes for each line