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.
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.
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
ExampleBEGIN
DBMS_OUTPUT.DISABLE;
END;
ENABLE
The
ExamplesENABLE
stored procedure is used to enable the capability to send messages to or receive
messages from the message buffer. Syntax:ENABLE [ (buffer_size INTEGER) ]
ParameterParameter | 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. |
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
ExamplesGET_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)
ParametersParameter | 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. |
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
ExamplesGET_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)
ParametersParameter | 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. |
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
ExamplePUT
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)
ParameterParameter | Description |
---|---|
item | Specifies the text to be written to the message buffer. |
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
ExamplePUT_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)
ParameterParameter | Description |
---|---|
item | Specifies the text to be written to the message buffer. |
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
ParameterSERVEROUTPUT
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*PlusSET
command, not by a stored procedure as implemented in PolarDB. - In PSQL, the default setting is
SERVEROUTPUT (TRUE)
.
Parameter | Description |
---|---|
stdout | Specifies the destination of a message sent by calling the PUT , PUT_LINE , or NEW_LINE stored procedure. Valid values:
|
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