7.7 DBMS_OUTPUT

Table of Contents Previous Next


7 Built-In Packages : 7.7 DBMS_OUTPUT

The DBMS_OUTPUT package provides the capability to send messages (lines of text) to a message buffer, or get 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.
ENABLE(buffer_size)
GET_LINE(line OUT, status OUT)
GET_LINES(lines OUT, numlines IN OUT)
PUT(item)
Direct messages from PUT, PUT_LINE, or NEW_LINE to either standard output or the message buffer.
7.7.1 CHARARR
The CHARARR is for storing multiple message lines.
7.7.2 DISABLE
The DISABLE procedure clears out the message buffer. Any messages in the buffer at the time the DISABLE procedure is executed will no longer be accessible. Any messages subsequently 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 have been disabled.
Use the ENABLE procedure or SERVEROUTPUT(TRUE) procedure to re-enable the sending and receiving of messages.
7.7.3 ENABLE
The ENABLE procedure enables the capability to send messages to the message buffer 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 upon the state of SERVEROUTPUT.
If the last state of SERVEROUTPUT is TRUE, the message goes to standard output of the command line.
If the last state of SERVEROUTPUT is FALSE, the message goes to the message buffer.
ENABLE [ (buffer_size INTEGER) ]
Maximum length of the message buffer in bytes. If a buffer_size of less than 2000 is specified, the buffer size is set to 2000.
The following anonymous block enables messages. Setting SERVEROUTPUT(TRUE) forces them to standard output.
The following anonymous block enables messages, but setting SERVEROUTPUT(FALSE) directs messages to the message buffer.
7.7.4 GET_LINE
The GET_LINE procedure provides the capability to retrieve a line of text from the message buffer. Only text that has been terminated by an end-of-line character sequence is retrieved – that is complete lines generated using PUT_LINE, or by a series of PUT calls followed by a NEW_LINE call.
GET_LINE(line OUT VARCHAR2, status OUT INTEGER)
The following anonymous block writes the emp table out to the message buffer as a comma-delimited string for each row.
7.7.5 GET_LINES
The GET_LINES procedure provides the capability to retrieve one or more lines of text from the message buffer into a collection. Only text that has been terminated by an end-of-line character sequence is retrieved – that is complete lines generated using PUT_LINE, or by a series of PUT calls followed by a NEW_LINE call.
GET_LINES(lines OUT CHARARR, numlines IN OUT INTEGER)
Actual number of lines retrieved from the message buffer. If the output value of numlines is less than the input value, then there are no more lines left in the message buffer.
The following example uses the GET_LINES procedure to store all rows from the emp table that were placed on the message buffer, into an array.
7.7.6 NEW_LINE
The NEW_LINE procedure writes an end-of-line character sequence in the message buffer.
The NEW_LINE procedure expects no parameters.
7.7.7 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.
PUT(item VARCHAR2)
The following example uses the PUT procedure to display a comma-delimited list of employees from the emp table.
7.7.8 PUT_LINE
The PUT_LINE procedure writes a single line to the message buffer including an end-of-line character sequence.
PUT_LINE(item VARCHAR2)
The following example uses the PUT_LINE procedure to display a comma-delimited list of employees from the emp table.
The SERVEROUTPUT procedure provides the capability to direct 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 is implementation dependent. For example, in Oracle SQL*Plus, SERVEROUTPUT(FALSE) is the default. In PSQL, SERVEROUTPUT(TRUE) is the default. Also note that in Oracle SQL*Plus, this setting is controlled using the SQL*Plus SET command, not by a stored procedure as implemented in Advanced Server.
SERVEROUTPUT(stdout BOOLEAN)
Set to TRUE if subsequent PUT, PUT_LINE, or NEW_LINE commands are to send text directly to standard output of the command line. Set to FALSE if text is to be sent to the message buffer.

7 Built-In Packages : 7.7 DBMS_OUTPUT

Table of Contents Previous Next