The built-in DBMS_OUTPUT package is used to send a message to or retrieve a message from the message buffer.
Subprograms
Subprogram | Description |
ENABLE Procedure | Enables message output. |
DISABLE Procedure | Disables message output. |
PUT Procedure | Inputs a message to the buffer. |
PUT_LINE Procedure | Outputs a message in the buffer. |
NEW_LINE Procedure | Inputs a line break to the buffer. |
GET_LINE Procedure | Retrieves a line of messages from the buffer. |
GET_LINES Procedure | Retrieves an array of all rows from the buffer. |
The SERVEROUTPUT parameter specifies the destination of an output message that is generated during program execution.
If this parameter is set to ON, the message is sent to the standard output. In PolarDB, this parameter is set to ON by default.If this parameter is set to OFF, the message is sent to the buffer.
ENABLE Procedure
This stored procedure is used to enable the calling of the PUT, PUT_LINE, NEW_LINE, and GET_LINE stored procedures.
Syntax
DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000);Parameters
Parameter | Description |
buffer_size | (Optional) The maximum length of the message buffer. Default value: 20000. Unit: bytes. |
Example
The following example shows how to use DBMS_OUTPUT.ENABLE to call the message output stored procedure:
-- Hello, PolarDB!
BEGIN
DBMS_OUTPUT.ENABLE;
SET SERVEROUTPUT = ON;
DBMS_OUTPUT.PUT_LINE('Hello, PolarDB!');
END;When the SERVEROUTPUT parameter is enabled, you do not need to call DBMS_OUTPUT.ENABLE( ) to enable the calling of the PUT, PUT_LINE, NEW_LINE, and GET_LINE stored procedures. DISABLE Procedure
This stored procedure is used to clean the buffer and disable message output. You can configure the ENABLE or SERVEROUTPUT parameter to re-enable message output.
Syntax
DBMS_OUTPUT.DISABLE();Example
The following example shows how to disable message output:
-- NULL
BEGIN
SET SERVEROUTPUT = OFF;
DBMS_OUTPUT.DISABLE;
DBMS_OUTPUT.PUT_LINE('Hello, PolarDB!');
END;PUT Procedure
This stored procedure is used to input a message to the buffer.
Syntax
DBMS_OUTPUT.PUT (item IN VARCHAR2);Parameters
Parameter | Description |
item | The message that you want to input to the buffer. |
Example
The following example shows how to input part of line messages to the buffer:
-- Hello, PolarDB!
BEGIN
DBMS_OUTPUT.PUT('Hello, ');
DBMS_OUTPUT.PUT('PolarDB!');
DBMS_OUTPUT.NEW_LINE;
END;PUT_LINE Procedure
This stored procedure is used to output a message in the buffer.
Syntax
DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2);Parameters
Parameter | Description |
item | The message that you want to output. |
Example
This example shows how to output a line of messages in the buffer:
-- Hello, PolarDB!
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, PolarDB!');
END;NEW_LINE Procedure
This stored procedure is used to write a line break to the buffer.
Syntax
DBMS_OUTPUT.NEW_LINE();Example
The following example shows how to write a line break to the buffer:
-- Hello, PolarDB!
BEGIN
DBMS_OUTPUT.PUT('Hello, PolarDB!');
DBMS_OUTPUT.NEW_LINE;
END;GET_LINE Procedure
This stored procedure is used to retrieve a line of messages from the buffer.
Syntax
DBMS_OUTPUT.GET_LINE (line OUT VARCHAR2,
status OUT INTEGER);Parameters
Parameter | Description |
line | 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, the value 0 is returned. If no text is returned, the value 1 is returned. |
Example
The following example shows how to retrieve data from the buffer and insert the data into a variable:
DECLARE
line VARCHAR2;
status INTEGER;
BEGIN
line := '';
set SERVEROUTPUT = OFF;
DBMS_OUTPUT.PUT_LINE('Hello, PolarDB!');
set SERVEROUTPUT = ON;
DBMS_OUTPUT.GET_LINE(line, status);
-- line: Hello, PolarDB! status: 0
DBMS_OUTPUT.PUT_LINE(line || ' ' ||status);
DBMS_OUTPUT.GET_LINE(line, status);
-- status is 1
DBMS_OUTPUT.PUT_LINE(line || ' ' ||status);
END;GET_LINES Procedure
This stored procedure is used to retrieve an array of rows from the buffer.
Syntax
DBMS_OUTPUT.GET_LINE (lines OUT VARCHAR2[],
numlines INOUT INTEGER);Parameters
Parameter | Description |
lines | The array that is used to receive the line of text from the message buffer. |
numlines | If this parameter is set to IN, the system returns the number of the lines of text that you want to retrieve from the buffer. If this parameter is set to OUT, the system returns the number of the lines of text that are actually read. |
Example
The following example shows how to retrieve an array of rows from the buffer:
DECLARE
lines VARCHAR2[];
numline INTEGER;
item VARCHAR2;
BEGIN
numline := 3;
SET SERVEROUTPUT = OFF;
DBMS_OUTPUT.PUT_LINE('Hello');
DBMS_OUTPUT.PUT_LINE('PolarDB');
SET SERVEROUTPUT = ON;
DBMS_OUTPUT.GET_LINES(lines, numline);
-- Hello
-- PolarDB
-- Total lines: 2
FOREACH item in ARRAY lines LOOP
DBMS_OUTPUT.PUT_LINE(item);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total lines:' || numline);
END;