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.
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.
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
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)
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