3 Built-In Packages : 3.9 DBMS_OUTPUT
3.9 DBMS_OUTPUTThe 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) PUT_LINE(item) SERVEROUTPUT(stdout) 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.
3.9.1 CHARARRThe CHARARR is for storing multiple message lines.3.9.2 DISABLEThe 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.3.9.3 ENABLEThe 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 same effect could have been achieved by simply using SERVEROUTPUT(TRUE).The following anonymous block enables messages, but setting SERVEROUTPUT(FALSE) directs messages to the message buffer.3.9.4 GET_LINEThe 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.The following 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.3.9.5 GET_LINESThe 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)Table receiving the lines of text from the message buffer. See CHARARR for a description of lines.numlines INnumlines OUTActual 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.3.9.6 NEW_LINEThe NEW_LINE procedure writes an end-of-line character sequence in the message buffer.The NEW_LINE procedure expects no parameters.3.9.7 PUTThe 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.3.9.8 PUT_LINEThe 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.3.9.9 SERVEROUTPUTThe 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.The following anonymous block sends the first message to the command line and the second message to the message buffer.If within the same session, the following anonymous block is executed, the message stored in the message buffer from the prior example is flushed and displayed on the command line as well as the new message.
3 Built-In Packages : 3.9 DBMS_OUTPUT