DBMS_OUTPUT - Developer Guide| Alibaba Cloud Documentation Center

The DBMS_OUTPUT package provides the capability to send messages (lines of text) to a message buffer, or to retrieve messages from the message buffer. A message buffer is local to a single session. You can use the DBMS_PIPE package to send messages between sessions.

The following table lists the functions and stored procedures that are available in the DBMS_OUTPUT package.

Table 1. DBMS_OUTPUT functions and stored procedures
Function/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 from the message buffer.
GET LINES(lines OUT, numlines IN OUT)	N/A	Retrieves multiple lines from the message buffer.
NEW LINE	N/A	Puts an end-of-line character sequence.
PUT(item)	N/A	Puts a partial line without an end-of-line character sequence.
PUT LINE(item)	N/A	Puts a complete line with an end-of-line character sequence.
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_SQL package.

Table 2. DBMS_OUTPUT public variables
Public variable	Data type	Value	Description
chararr	TABLE		For message lines.

CHARARR

The CHARARR variable is used to store multiple message lines.

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

DISABLE

The DISABLE stored procedure clears out the message buffer. Any messages in the buffer at the time when the DISABLE stored procedure is called will no longer be accessible. Any messages subsequently sent with the PUT, PUT_LINE, or NEW_LINE stored procedure are discarded. When the PUT, PUT_LINE, or NEW_LINE stored procedure is called, no error is returned to the sender and the sending and receiving of messages are disabled.

You can use the ENABLE or SERVEROUTPUT(TRUE) stored procedure to re-enable the sending and receiving of messages.

DISABLE

Examples

The following anonymous block disables the sending and receiving of messages in the current session.

BEGIN
    DBMS_OUTPUT.DISABLE;
END;

ENABLE

The ENABLE stored procedure enables the capability to send messages to the message buffer or receive messages from the message buffer. Setting SERVEROUTPUT(TRUE) also performs an implicit call of the ENABLE stored procedure.

The status of the SERVEROUTPUT stored procedure depends the destination of a message sent with the PUT, PUT_LINE, or NEW_LINE procedure.

If the last status of the SERVEROUTPUT stored procedure is TRUE, the message is sent to the standard output of the command line.
If the last status of the SERVEROUTPUT stored procedure is FALSE, the message is sent to the message buffer.

ENABLE [ (buffer_size INTEGER) ]

Parameters


Parameter	Description
buffer_size	The maximum length of the message buffer. Unit: byte. If the specified value of the buffer_size parameter is less than 2000, the buffer size is set to 2000.

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, the SERVEROUTPUT (FALSE) stored procedure is called 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 provides the capability to retrieve a line of text from the message buffer. Only text that has been terminated by an end-of-line character sequence is retrieved. The text is a complete line that is generated by using the PUT_LINE stored procedure, or by a series of PUT calls followed by a NEW_LINE call.

GET_LINE(line OUT VARCHAR2, status OUT INTEGER)

Parameters


Parameter	Description
line	The variable used to receive the line of text from the message buffer.
status	If a line of text was returned from the message buffer, the value is 0. If no text was returned, the value is 1.

Examples

The following anonymous block writes the emp table to the message buffer as a comma-delimited string for each row.

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 rows in the message 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;

SELECT msg FROM messages;

                               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 provides the capability to retrieve one or more lines of text from the message buffer into a collection. Only text that has been terminated by an end-of-line character sequence is retrieved. The text is a complete line that is generated by using the PUT_LINE stored procedure, or by a series of PUT calls followed by a NEW_LINE call.

GET_LINES(lines OUT CHARARR, numlines IN OUT INTEGER)

Parameters


Parameter	Description
lines	The table that receives the lines of text from the message buffer. For more information about the lines parameter, see CHARARR.
numlines IN	The number of lines to be retrieved from the message buffer.
numlines OUT	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.

Examples

The following example uses the GET_LINES stored procedure to store all rows from the emp table that were placed on the message buffer, into 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;

SELECT msg FROM messages;

                               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 writes an end-of-line character sequence in the message buffer.

NEW_LINE

Parameters

The NEW_LINE stored procedure requires no parameters.

PUT

The PUT stored procedure writes a string to the message buffer. No end-of-line character sequence is written at the end of the string. You can use the NEW_LINE stored procedure to add an end-of-line character sequence.

PUT(item VARCHAR2)

Parameters


Parameter	Description
item	The text written to the message buffer.

Examples

The following example uses the PUT stored procedure to display a comma-delimited 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;

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 writes a single line to the message buffer including an end-of-line character sequence.

PUT_LINE(item VARCHAR2)

Parameters


Parameter	Description
item	The text to be written to the message buffer.

Examples

The following example uses the PUT_LINE stored procedure to display a comma-delimited list of employees 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;

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 provides the capability to direct messages to the standard output of the command line or to the message buffer. Setting SERVEROUTPUT(TRUE) also performs an implicit call of the ENABLE stored procedure.

The default setting of the SERVEROUTPUT stored procedure is implementation dependent. For example, in Oracle SQL*Plus, the default setting is SERVEROUTPUT(FALSE). In psql, the default setting is SERVEROUTPUT (TRUE). Note that in Oracle SQL*Plus, this setting is controlled by using the SQL*Plus SET command, not by a stored procedure as implemented in PolarDB-O.

SERVEROUTPUT(stdout BOOLEAN)

Parameters


Parameter	Description
stdout	To ensure that subsequent PUT, PUT_LINE, or NEW_LINE commands send text to the standard output of the command line, you need to set this parameter to TRUE. To send text to the message buffer, you need to set this parameter to FALSE.

Examples

The following anonymous block sends the first message to 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;

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 from the preceding example is flushed. This message is displayed on the command line as a new message.

BEGIN
    DBMS_OUTPUT.SERVEROUTPUT(TRUE);
    DBMS_OUTPUT.PUT_LINE('Flush messages from the buffer');
END;

This message goes to the message buffer
Flush messages from the buffer