DBMS_OUTPUT v17
The DBMS_OUTPUT
package sends messages (lines of text) to a message buffer or gets messages from the message buffer. A message buffer is local to a single session. Use the DBMS_PIPE
package to send messages between sessions.
The procedures and functions available in the DBMS_OUTPUT
package are listed in the following table.
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 variables available in the DBMS_OUTPUT
package.
Public variables | Data type | Value | Description |
---|---|---|---|
chararr | TABLE | For message lines. |
CHARARR
The CHARARR
is for storing multiple message lines.
DISABLE
The DISABLE
procedure clears out the message buffer. You can no longer access any messages in the buffer at the time the DISABLE
procedure is executed. Any messages later sent with the PUT
, PUT_LINE,
or NEW_LINE
procedures are discarded. No error is returned to the sender when the PUT
, PUT_LINE,
or NEW_LINE
procedures are executed and messages were disabled.
Use the ENABLE or
SERVEROUTPUT(TRUE)` procedure to reenable sending and receiving messages.
Examples
This anonymous block disables sending and receiving messages in the current session.
ENABLE
The ENABLE
procedure enables you to send messages to or retrieve messages from the message buffer. Running SERVEROUTPUT(TRUE)
also implicitly performs the ENABLE
procedure.
The destination of a message sent with PUT
, PUT_LINE
, or NEW_LINE
depends on the state of SERVEROUTPUT
.
- If the last state of
SERVEROUTPUT
isTRUE
, the message goes to standard output of the command line. - If the last state of
SERVEROUTPUT
isFALSE
, the message goes to the message buffer.
Parameter
buffer_size
Maximum length of the message buffer in bytes. If you specify a buffer_size
of less than 2000, the buffer size is set to 2000.
Examples
This anonymous block enables messages. Setting SERVEROUTPUT(TRUE)
forces them to standard output.
You can achieve the same effect by using SERVEROUTPUT(TRUE)
.
This anonymous block enables messages, but setting SERVEROUTPUT(FALSE)
directs messages to the message buffer.
GET_LINE
The GET_LINE
procedure retrieves a line of text from the message buffer. Only text that was terminated by an end-of-line character sequence is retrieved. That includes complete lines generated using PUT_LINE
or by a series of PUT
calls followed by a NEW_LINE
call.
Parameters
line
Variable receiving the line of text from the message buffer.
status
0
if a line was returned from the message buffer, 1
if there was no line to return.
Examples
This anonymous block writes the emp
table out to the message buffer as a comma-delimited string for each row.
This anonymous block reads the message buffer and inserts the messages written by the prior example into a table named messages
. The rows in messages
are then displayed.
GET_LINES
The GET_LINES
procedure retrieves one or more lines of text from the message buffer into a collection. Only text that was terminated by an end-of-line character sequence is retrieved. That includes complete lines generated using PUT_LINE
or by a series of PUT
calls followed by a NEW_LINE
call.
Parameters
lines
Table receiving the lines of text from the message buffer. See CHARARR
for a description of lines.
numlines IN
Number of lines to retrieve from the message buffer.
numlines OUT
Actual number of lines retrieved from the message buffer. If the output value of numlines
is less than the input value, then no more lines are left in the message buffer.
Examples
This example uses the GET_LINES
procedure to store all rows from the emp
table that were placed in the message buffer into an array.
NEW_LINE
The NEW_LINE
procedure writes an end-of-line character sequence in the message buffer.
Parameter
The NEW_LINE
procedure expects no parameters.
PUT
The PUT
procedure writes a string to the message buffer. No end-of-line character sequence is written at the end of the string. Use the NEW_LINE
procedure to add an end-of-line character sequence.
Parameter
item
Text written to the message buffer.
Examples
The following example uses the PUT
procedure to display a comma-delimited list of employees from the emp
table.
PUT_LINE
The PUT_LINE
procedure writes a single line to the message buffer including an end-of-line character sequence.
Parameter
item
Text to write to the message buffer.
Examples
This example uses the PUT_LINE
procedure to display a comma-delimited list of employees from the emp
table.
SERVEROUTPUT
The SERVEROUTPUT
procedure directs messages to standard output of the command line or to the message buffer. Setting SERVEROUTPUT(TRUE)
also performs an implicit execution of ENABLE
.
The default setting of SERVEROUTPUT
depends on the implementation. For example, in Oracle SQL*Plus, SERVEROUTPUT(FALSE)
is the default. In PSQL, SERVEROUTPUT(TRUE)
is the default. Also, in Oracle SQL*Plus, you control this setting using the SQL*Plus SET
comman, not by a stored procedure as implemented in EDB Postgres Advanced Server.
To get an Oracle-style display output, you can set the dbms_output.serveroutput
to FALSE
in the postgresql.conf
file, which disables the message output. The default is TRUE
, which enables the message output.
Parameter
stdout
Set to TRUE
if you want subsequent PUT
, PUT_LINE
, or NEW_LINE
to send text directly to standard output of the command line. Set to FALSE
to send text to the message buffer.
Examples
This anonymous block sends the first message to the command line and the second message to the message buffer.
If, in the same session, the following anonymous block is executed, the message stored in the message buffer from the prior example is flushed. It's displayed on the command line along with the new message.