DBMS_OUTPUT - Developer Guide| Alibaba Cloud Documentation Center

The DBMS_OUTPUT package is used to send messages to a message buffer or retrieve messages from the message buffer. Messages are lines of text.

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/Procedures
Function/Procedure	Return Type	Description
DISABLE	n/a	Disable the capability to send and receive messages.
ENABLE(buffer size)	n/a	Enable the capability to send and receive messages.
GET LINE(line OUT, status OUT)	n/a	Get a line from the message buffer.
GET LINES(lines OUT, numlines IN OUT)	n/a	Get 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	Direct 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. DBMS OUTPUT Public Variables
Public Variables	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. When you call the DISABLE stored procedure, the messages in the buffer cannot be accessed. After you call the DISABLE stored procedure, messages that are sent by calling 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 and the messages in the buffer become invalid.

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. When you call the SERVEROUTPUT(TRUE) stored procedure, the ENABLE stored procedure is also implicitly called.

The status of the SERVEROUTPUT stored procedure determines the destination of a message sent by calling the PUT, PUT_LINE, or NEW_LINE stored 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 parameter is automatically 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, 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 from the message buffer. Only text that is terminated by an end-of-line character sequence can be 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 is returned from the message buffer, the value is 0. If no text is 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 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;

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 is terminated by an end-of-line character sequence can be 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

In the following example, the GET_LINES stored procedure is used to retrieve all rows from the emp table in the message buffer and store the rows 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;

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 characters are written at the end of the string. You can use the NEW_LINE stored procedure to add an end-of-line character.

PUT(item VARCHAR2)

Parameters


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

Examples

In the following example, the PUT stored procedure is used 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 is used to write a line that contains an end-of-line character to the message buffer.

PUT_LINE(item VARCHAR2)

Parameters


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

Examples

In the following example, the PUT_LINE stored procedure is used to display a comma-delimited list of employees. The data is 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;

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.

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). In Oracle SQL*Plus, this setting is controlled by using the SQL*Plus SET command, not by a stored procedure as implemented in PolarDB.

SERVEROUTPUT(stdout BOOLEAN)

Table 3. 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, set this parameter to true. To send text to the message buffer, 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 in the message buffer in the preceding example is cleared. This message appears in 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