3 DBMS_OUTPUT

Using DBMS_OUTPUT

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

• Overview

• Operational notes

• Exceptions

• Rules and limits

• Example

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 of an external tool like ttIsql to control whether to display output.

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:

Example

Example: Debugging stored procedures

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.

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

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

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

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

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.

Syntax

DBMS_OUTPUT.PUT (
    a IN VARCHAR2);

Parameters

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

PUT_LINE procedure

This procedure places a line in the buffer.

Syntax

DBMS_OUTPUT.PUT_LINE (
   a IN VARCHAR2);

Parameters

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