The DBMS_OUTPUT package is used to send messages to or retrieve messages from a message buffer.

The following table describes the functions and stored procedures that are available in the DBMS_OUTPUT package.
Table 1. Functions and stored procedures in the DBMS OUTPUT package
Function or stored procedure Return type Description
DISABLE N/A Disables the capability to send and receive messages.
ENABLE(buffer size) N/A Enables the capability to send and receive messages.
GET_LINE(line OUT, status OUT) N/A Retrieves a line of text from the message buffer.
GET_LINES(lines OUT, numlines IN OUT) N/A Retrieves multiple lines of text from the message buffer.
NEW_LINE N/A Inserts an end-of-line sequence.
PUT(item) N/A Inserts a partial line without an end-of-line sequence in the message buffer.
PUT_LINE(item) N/A Inserts a complete line with an end-of-line sequence in the message buffer.
SERVEROUTPUT(stdout) N/A Directs messages from PUT, PUT_LINE, or NEW_LINE to either standard output or the message buffer.
The following table lists the public variable that is available in the DBMS_OUTPUT package.
Table 2. Public variable in the DBMS_OUTPUT package
Variable Variable type Variable value Description
chararr TABLE - Stores multiple message lines.

CHARARR

The CHARARR variable is used to store multiple message lines. Syntax:
TYPE chararr IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;

DISABLE

The DISABLE stored procedure is used to clear out the message buffer. When you call the DISABLE stored procedure, messages in the message buffer cannot be accessed.

You can use the ENABLE or SERVEROUTPUT(TRUE) stored procedure to re-enable the sending and receiving of messages. Syntax:
DISABLE
Example
BEGIN
    DBMS_OUTPUT.DISABLE;
END;

ENABLE

The ENABLE stored procedure is used to enable the capability to send messages to or receive messages from the message buffer. Syntax:
ENABLE [ (buffer_size INTEGER) ]
Parameter
Parameter Description
buffer_size Specifies the maximum size of the message buffer. Unit: bytes. If the specified value of the buffer_size parameter is less than 2,000, the parameter is automatically set to 2,000.
Examples
The following anonymous block enables the sending and receiving of messages. The SERVEROUTPUT(TRUE) stored procedure is configured to force messages to standard output.
BEGIN
    DBMS_OUTPUT.ENABLE;
    DBMS_OUTPUT.SERVEROUTPUT(TRUE);
    DBMS_OUTPUT.PUT_LINE('Messages enabled');
END;

Messages enabled
To achieve the same effect, you can also use only the SERVEROUTPUT(TRUE) stored procedure.
BEGIN
    DBMS_OUTPUT.SERVEROUTPUT(TRUE);
    DBMS_OUTPUT.PUT_LINE('Messages enabled');
END;

Messages enabled
The following anonymous block enables the sending and receiving of messages. However, you can call the SERVEROUTPUT (FALSE) stored procedure to send messages to the message buffer.
BEGIN
    DBMS_OUTPUT.ENABLE;
    DBMS_OUTPUT.SERVEROUTPUT(FALSE);
    DBMS_OUTPUT.PUT_LINE('Message sent to buffer');
END;

GET_LINE

The GET_LINE stored procedure is used to retrieve a line of text with an end-of-line sequence from the message buffer. Syntax:
GET_LINE(line OUT VARCHAR2, status OUT INTEGER)
Parameters
Parameter Description
line Specifies the variable that is used to receive the line of text from the message buffer.
status If a line of text is returned from the message buffer, 0 is returned. If no text is returned, 1 is returned.
Examples
The following anonymous block converts the emp table to a comma-separated list and writes the list as a string to the message buffer.
EXEC DBMS_OUTPUT.SERVEROUTPUT(FALSE);

DECLARE
    v_emprec        VARCHAR2(120);
    CURSOR emp_cur IS SELECT * FROM emp ORDER BY empno;
BEGIN
    DBMS_OUTPUT.ENABLE;
    FOR i IN emp_cur LOOP
        v_emprec := i.empno || ',' || i.ename || ',' || i.job || ',' ||
            NVL(LTRIM(TO_CHAR(i.mgr,'9999')),'') || ',' || i.hiredate ||
            ',' || i.sal || ',' ||
            NVL(LTRIM(TO_CHAR(i.comm,'9990.99')),'') || ',' || i.deptno;
        DBMS_OUTPUT.PUT_LINE(v_emprec);
    END LOOP;
END;
The following anonymous block reads the message buffer and inserts the messages written by the preceding example into a table named messages. The lines of text in the messages table are then displayed.
CREATE TABLE messages (
    status          INTEGER,
    msg             VARCHAR2(100)
);

DECLARE
    v_line          VARCHAR2(100);
    v_status        INTEGER := 0;
BEGIN
    DBMS_OUTPUT.GET_LINE(v_line,v_status);
    WHILE v_status = 0 LOOP
        INSERT INTO messages VALUES(v_status, v_line);
        DBMS_OUTPUT.GET_LINE(v_line,v_status);
    END LOOP;
END;
Use SELECT to query data in the messages table:
SELECT msg FROM messages;
The following output is displayed:
                               msg
-----------------------------------------------------------------
 7369,SMITH,CLERK,7902,17-DEC-80 00:00:00,800.00,,20
 7499,ALLEN,SALESMAN,7698,20-FEB-81 00:00:00,1600.00,300.00,30
 7521,WARD,SALESMAN,7698,22-FEB-81 00:00:00,1250.00,500.00,30
 7566,JONES,MANAGER,7839,02-APR-81 00:00:00,2975.00,,20
 7654,MARTIN,SALESMAN,7698,28-SEP-81 00:00:00,1250.00,1400.00,30
 7698,BLAKE,MANAGER,7839,01-MAY-81 00:00:00,2850.00,,30
 7782,CLARK,MANAGER,7839,09-JUN-81 00:00:00,2450.00,,10
 7788,SCOTT,ANALYST,7566,19-APR-87 00:00:00,3000.00,,20
 7839,KING,PRESIDENT,,17-NOV-81 00:00:00,5000.00,,10
 7844,TURNER,SALESMAN,7698,08-SEP-81 00:00:00,1500.00,0.00,30
 7876,ADAMS,CLERK,7788,23-MAY-87 00:00:00,1100.00,,20
 7900,JAMES,CLERK,7698,03-DEC-81 00:00:00,950.00,,30
 7902,FORD,ANALYST,7566,03-DEC-81 00:00:00,3000.00,,20
 7934,MILLER,CLERK,7782,23-JAN-82 00:00:00,1300.00,,10
(14 rows)

GET_LINES

The GET_LINES stored procedure is used to retrieve one or more lines of text from the message buffer into a collection. Syntax:
GET_LINES(lines OUT CHARARR, numlines IN OUT INTEGER)
Parameters
Parameter Description
lines Specifies the table that receives the lines of text from the message buffer. For more information about the lines parameter, see the CHARARR variable.
numlines IN Specifies the number of lines to be retrieved from the message buffer.
numlines OUT Specifies the number of lines retrieved from the message buffer. If the output value of the numlines parameter is less than the input value, then the message buffer contains no more lines of text.
Examples
In the following example, the GET_LINES stored procedure is used to retrieve all lines of text from the emp table in the message buffer and store the lines of text in an array.
EXEC DBMS_OUTPUT.SERVEROUTPUT(FALSE);

DECLARE
    v_emprec        VARCHAR2(120);
    CURSOR emp_cur IS SELECT * FROM emp ORDER BY empno;
BEGIN
    DBMS_OUTPUT.ENABLE;
    FOR i IN emp_cur LOOP
        v_emprec := i.empno || ',' || i.ename || ',' || i.job || ',' ||
            NVL(LTRIM(TO_CHAR(i.mgr,'9999')),'') || ',' || i.hiredate ||
            ',' || i.sal || ',' ||
            NVL(LTRIM(TO_CHAR(i.comm,'9990.99')),'') || ',' || i.deptno;
        DBMS_OUTPUT.PUT_LINE(v_emprec);
    END LOOP;
END;

DECLARE
    v_lines         DBMS_OUTPUT.CHARARR;
    v_numlines      INTEGER := 14;
    v_status        INTEGER := 0;
BEGIN
    DBMS_OUTPUT.GET_LINES(v_lines,v_numlines);
    FOR i IN 1..v_numlines LOOP
        INSERT INTO messages VALUES(v_numlines, v_lines(i));
    END LOOP;
END;
Use SELECT to query data in the messages table:
SELECT msg FROM messages;
The following output is displayed:
                               msg
-----------------------------------------------------------------
 7369,SMITH,CLERK,7902,17-DEC-80 00:00:00,800.00,,20
 7499,ALLEN,SALESMAN,7698,20-FEB-81 00:00:00,1600.00,300.00,30
 7521,WARD,SALESMAN,7698,22-FEB-81 00:00:00,1250.00,500.00,30
 7566,JONES,MANAGER,7839,02-APR-81 00:00:00,2975.00,,20
 7654,MARTIN,SALESMAN,7698,28-SEP-81 00:00:00,1250.00,1400.00,30
 7698,BLAKE,MANAGER,7839,01-MAY-81 00:00:00,2850.00,,30
 7782,CLARK,MANAGER,7839,09-JUN-81 00:00:00,2450.00,,10
 7788,SCOTT,ANALYST,7566,19-APR-87 00:00:00,3000.00,,20
 7839,KING,PRESIDENT,,17-NOV-81 00:00:00,5000.00,,10
 7844,TURNER,SALESMAN,7698,08-SEP-81 00:00:00,1500.00,0.00,30
 7876,ADAMS,CLERK,7788,23-MAY-87 00:00:00,1100.00,,20
 7900,JAMES,CLERK,7698,03-DEC-81 00:00:00,950.00,,30
 7902,FORD,ANALYST,7566,03-DEC-81 00:00:00,3000.00,,20
 7934,MILLER,CLERK,7782,23-JAN-82 00:00:00,1300.00,,10
(14 rows)

NEW_LINE

The NEW_LINE stored procedure is used to write an end-of-line sequence in the message buffer. Syntax:
NEW_LINE

PUT

The PUT stored procedure is used to write a string to the message buffer. You can use the NEW_LINE stored procedure to add an end-of-line sequence. Syntax:
PUT(item VARCHAR2)
Parameter
Parameter Description
item Specifies the text to be written to the message buffer.
Example
In the following example, the PUT stored procedure is used to display a comma-separated list of employees from the emp table.
DECLARE
    CURSOR emp_cur IS SELECT * FROM emp ORDER BY empno;
BEGIN
    FOR i IN emp_cur LOOP
        DBMS_OUTPUT.PUT(i.empno);
        DBMS_OUTPUT.PUT(',');
        DBMS_OUTPUT.PUT(i.ename);
        DBMS_OUTPUT.PUT(',');
        DBMS_OUTPUT.PUT(i.job);
        DBMS_OUTPUT.PUT(',');
        DBMS_OUTPUT.PUT(i.mgr);
        DBMS_OUTPUT.PUT(',');
        DBMS_OUTPUT.PUT(i.hiredate);
        DBMS_OUTPUT.PUT(',');
        DBMS_OUTPUT.PUT(i.sal);
        DBMS_OUTPUT.PUT(',');
        DBMS_OUTPUT.PUT(i.comm);
        DBMS_OUTPUT.PUT(',');
        DBMS_OUTPUT.PUT(i.deptno);
        DBMS_OUTPUT.NEW_LINE;
    END LOOP;
END;
The following output is displayed:
7369,SMITH,CLERK,7902,17-DEC-80 00:00:00,800.00,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81 00:00:00,1600.00,300.00,30
7521,WARD,SALESMAN,7698,22-FEB-81 00:00:00,1250.00,500.00,30
7566,JONES,MANAGER,7839,02-APR-81 00:00:00,2975.00,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81 00:00:00,1250.00,1400.00,30
7698,BLAKE,MANAGER,7839,01-MAY-81 00:00:00,2850.00,,30
7782,CLARK,MANAGER,7839,09-JUN-81 00:00:00,2450.00,,10
7788,SCOTT,ANALYST,7566,19-APR-87 00:00:00,3000.00,,20
7839,KING,PRESIDENT,,17-NOV-81 00:00:00,5000.00,,10
7844,TURNER,SALESMAN,7698,08-SEP-81 00:00:00,1500.00,0.00,30
7876,ADAMS,CLERK,7788,23-MAY-87 00:00:00,1100.00,,20
7900,JAMES,CLERK,7698,03-DEC-81 00:00:00,950.00,,30
7902,FORD,ANALYST,7566,03-DEC-81 00:00:00,3000.00,,20
7934,MILLER,CLERK,7782,23-JAN-82 00:00:00,1300.00,,10

PUT_LINE

The PUT_LINE stored procedure is used to write a line of text with an end-of-line sequence to the message buffer. Syntax:
PUT_LINE(item VARCHAR2)
Parameter
Parameter Description
item Specifies the text to be written to the message buffer.
Example

In the following example, the PUT_LINE stored procedure is used to display a comma-separated list of employees retrieved from the emp table.

DECLARE
    v_emprec        VARCHAR2(120);
    CURSOR emp_cur IS SELECT * FROM emp ORDER BY empno;
BEGIN
    FOR i IN emp_cur LOOP
        v_emprec := i.empno || ',' || i.ename || ',' || i.job || ',' ||
            NVL(LTRIM(TO_CHAR(i.mgr,'9999')),'') || ',' || i.hiredate ||
            ',' || i.sal || ',' ||
            NVL(LTRIM(TO_CHAR(i.comm,'9990.99')),'') || ',' || i.deptno;
        DBMS_OUTPUT.PUT_LINE(v_emprec);
    END LOOP;
END;
The following output is displayed:
7369,SMITH,CLERK,7902,17-DEC-80 00:00:00,800.00,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81 00:00:00,1600.00,300.00,30
7521,WARD,SALESMAN,7698,22-FEB-81 00:00:00,1250.00,500.00,30
7566,JONES,MANAGER,7839,02-APR-81 00:00:00,2975.00,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81 00:00:00,1250.00,1400.00,30
7698,BLAKE,MANAGER,7839,01-MAY-81 00:00:00,2850.00,,30
7782,CLARK,MANAGER,7839,09-JUN-81 00:00:00,2450.00,,10
7788,SCOTT,ANALYST,7566,19-APR-87 00:00:00,3000.00,,20
7839,KING,PRESIDENT,,17-NOV-81 00:00:00,5000.00,,10
7844,TURNER,SALESMAN,7698,08-SEP-81 00:00:00,1500.00,0.00,30
7876,ADAMS,CLERK,7788,23-MAY-87 00:00:00,1100.00,,20
7900,JAMES,CLERK,7698,03-DEC-81 00:00:00,950.00,,30
7902,FORD,ANALYST,7566,03-DEC-81 00:00:00,3000.00,,20
7934,MILLER,CLERK,7782,23-JAN-82 00:00:00,1300.00,,10

SERVEROUTPUT

The SERVEROUTPUT stored procedure is used to direct messages to the standard output of the command line or to the message buffer. After you configure the SERVEROUTPUT(TRUE) stored procedure, the ENABLE stored procedure can be implicitly called. Syntax:
SERVEROUTPUT(stdout BOOLEAN)
The default setting of the SERVEROUTPUT stored procedure is implementation-dependent. Examples:
  • In Oracle SQL*Plus, the default setting is SERVEROUTPUT(FALSE).
    Note This setting is controlled by using the SQL*Plus SET command, not by a stored procedure as implemented in PolarDB.
  • In PSQL, the default setting is SERVEROUTPUT (TRUE).
Parameter
Parameter Description
stdout Specifies the destination of a message sent by calling the PUT, PUT_LINE, or NEW_LINE stored procedure. Valid values:
  • TRUE: The message is sent to the standard output of the command line.
  • FALSE: The message is sent to the message buffer.
Examples

The following anonymous block sends the first message to the standard output of the command line and the second message to the message buffer.

BEGIN
    DBMS_OUTPUT.SERVEROUTPUT(TRUE);
    DBMS_OUTPUT.PUT_LINE('This message goes to the command line');
    DBMS_OUTPUT.SERVEROUTPUT(FALSE);
    DBMS_OUTPUT.PUT_LINE('This message goes to the message buffer');
END;
The following output is displayed:
This message goes to the command line
If the following anonymous block is executed within the same session, the message stored in the message buffer in the preceding example appears in the command line as a new message.
BEGIN
    DBMS_OUTPUT.SERVEROUTPUT(TRUE);
    DBMS_OUTPUT.PUT_LINE('Flush messages from the buffer');
END;
The following output is displayed:
This message goes to the message buffer
Flush messages from the buffer