Table of Contents
This chapter briefly introduces the DBMS_OUTPUT package, and describes how to use the procedures of the package.
DBMS_OUTPUT provides an interface for writing/reading a message to/from the buffer. It is possible for a procedure, function, or trigger to retrieve messages that were saved by a different procedure, function, or trigger.
This executes the ENABLE procedure of the DBMS_ OUTPUT package to allocate a message buffer of the specified size. If the size of the messages to save in the buffer exceeds the buffer size, an exception occurs.
The allocated buffer can be deleted by executing the DISABLE procedure, and if the GET_LINE, GET_LINES, PUT, PUT_LINE, or NEW_LINE procedure is called before calling the ENABLE procedure again, they will be ignored. Messages in the buffer can span multiple lines, and each line is terminated by an End-of-Line(EOL) character.
Messages written using the PUT_LINE procedure can be retrieved by executing the GET_LINE or GET_LINES procedure.
The following type is defined in the DBMS_OUTPUT package:
CHARARR
This type represents the space where data read from the message buffer is saved.
TYPE CHARARR IS TABLE OF VARCHAR2(32767)
This section describes the procedures provided by the DBMS_OUTPUT package, in alphabetical order.
Removes the allocated message buffer, and disables the use of other procedures of the DBMS_OUTPUT package. It deletes all remaining messages in the message buffer.
Details about the DISABLE procedure are as follows:
Prototype
DBMS_OUTPUT.DISABLE;
Example
BEGIN
DBMS_OUTPUT.PUT_LINE('Before DISABLE');
DBMS_OUTPUT.DISABLE;
DBMS_OUTPUT.PUT_LINE('After DISABLE');
END;
/
PSM completed
SQL>
Allocates a message buffer of the specified size, and enables the use of other procedures in the DBMS_OUTPUT package. If this procedure is called multiple times, the largest number is used as the size of the allocated message buffer. The size of the message buffer must be between two kilobytes and one megabyte.
Details about the ENABLE procedure are as follows:
Prototype
DBMS_OUTPUT.ENABLE
(
buffer_size IN INTEGER DEFAULT 20000
);
Parameter
| Parameter | Description |
|---|---|
| buffer_size | Allocated message buffer size, in bytes. |
Example
BEGIN
DBMS_OUTPUT.DISABLE;
DBMS_OUTPUT.PUT_LINE('Before ENABLE');
DBMS_OUTPUT.ENABLE(32768);
DBMS_OUTPUT.PUT_LINE('After ENABLE');
END;
/
After ENABLE
PSM completed
SQL>
Reads a line at a time from the message buffer. This procedure does not retrieve a message that does not form a complete line.
If the PUT or PUT_LINE procedure is called after the GET_LINE or GET_LINES procedure is called, all remaining messages are deleted from the message buffer.
The output parameter must be set according to the maximum allowed message size per line of 255 bytes.
Details about the GET_LINE and GET_LINES procedures are as follows:
Prototype
GET_LINE
Reads one line per execution.
DBMS_OUTPUT.GET_LINE
(
line OUT VARCHAR,
status OUT INTEGER
);
GET_LINES
Reads the specified number of lines in one execution, and returns the number of message lines that were actually read. If there are fewer message lines than the specified number, the actual number of message lines are saved.
DBMS_OUTPUT.GET_LINES
(
lines OUT CHARARR,
numlines IN OUT INTEGER
);
Parameter
| Parameter | Description |
|---|---|
| line, lines | Messages from a single or multiple lines are read from the message buffer. |
| status |
|
| numlines | Input: number of lines to read. Output: actual number of lines read. |
Example
GET_LINE
DECLARE
message VARCHAR(1024);
status INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE('A poet is the painter of the soul');
DBMS_OUTPUT.PUT_LINE('Faith without deeds is useless');
DBMS_OUTPUT.PUT_LINE('Forgiveness is better than revenge');
DBMS_OUTPUT.GET_LINE(message, status);
DBMS_OUTPUT.PUT_LINE(message);
END;
/
A poet is the painter of the soul
PSM completed
SQL>
GET_LINES
DECLARE
message_arr DBMS_OUTPUT.CHARARR;
num_lines INTEGER := 4;
BEGIN
DBMS_OUTPUT.PUT('A poet is ');
DBMS_OUTPUT.PUT('the painter of the soul');
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('Faith without deeds is useless');
DBMS_OUTPUT.PUT_LINE('Forgiveness is better than revenge');
DBMS_OUTPUT.GET_LINES(message_arr, num_lines);
DBMS_OUTPUT.PUT_LINE(message_arr(2));
END;
/
Faith without deeds is useless
PSM completed
SQL>
Saves an End-of-Line(EOL) marker in the message buffer.
If a message is written using the PUT procedure, it does not attach an EOL character, so the message cannot be retrieved with the GET_LINE or GET_LINES procedure. This procedure can be used to append an EOL character at the end of each line.
Details about the NEW_LINE procedure are as follows:
Prototype
DBMS_OUTPUT.NEW_LINE;
Example
BEGIN
DBMS_OUTPUT.PUT_LINE('The will of a man is his happiness');
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('Love your neighbor as yourself');
END;
/
The will of a man is his happiness
Love your neighbor as yourself
PSM completed
SQL>
Writes a message to the message buffer.
Details about the PUT and PUT_LINE procedures are as follows:
Prototype
PUT
Can write a single line via multiple calls. An EOL character is not added to the end of the last line of the message.
DBMS_OUTPUT.PUT
(
data IN NUMBER
);
DBMS_OUTPUT.PUT ( data IN VARCHAR );
PUT_LINE
Unlike the PUT procedure, this procedure adds an EOL character to the end of the last line of a message written to the buffer. To store only the EOL character, call the NEW_LINE procedure.
DBMS_OUTPUT.PUT_LINE ( data IN NUMBER );
DBMS_OUTPUT.PUT_LINE ( data IN VARCHAR );
Parameter
The available data types for the input parameter are NUMBER, VARCHAR, and DATE, and they are always converted to VARCHAR before being written to the buffer. NUMBER and DATE type data are converted using the TO_CHAR function before they are saved.
| Parameter | Description |
|---|---|
| data | Message to write to the message buffer. |
Exception
| Exception | Description |
|---|---|
| BUF_OVERFLOW | Occurs when the maximum message buffer size is exceeded. |