DBMS_OUTPUT module
The DBMS_OUTPUT module provides a set of procedures for putting messages (lines of text) in a message buffer and getting messages from the message buffer. These procedures are useful during application debugging when you need to write messages to standard output.
The schema for this module is SYSIBMADM.
The DBMS_OUTPUT module includes the following built-in routines.
Routine name | Description |
---|---|
DISABLE procedure | Disables the message buffer. |
ENABLE procedure | Enables the message buffer |
GET_LINE procedure | Gets a line of text from the message buffer. |
GET_LINES procedure | Gets one or more lines of text from the message buffer and places the text into a collection |
NEW_LINE procedure | Puts an end-of-line character sequence in the message buffer. |
PUT procedure | Puts a string that includes no end-of-line character sequence in the message buffer. |
PUT_LINE procedure | Puts a single line that includes an end-of-line character sequence in the message buffer. |
The procedures in this module allow you to work with the message buffer. Use the command line processor (CLP) command SET SERVEROUTPUT ON to redirect the output to standard output.
DISABLE and ENABLE procedures are not supported inside autonomous procedures.
An autonomous procedure is a procedure that, when called, executes inside a new transaction independent of the original transaction.
Example
In proc1 use the PUT and PUT_LINE procedures to put a line of text in the message buffer. When proc1 runs for the first time, SET SERVEROUTPUT ON is specified, and the line in the message buffer is printed to the CLP window. When proc1 runs a second time, SET SERVEROUTPUT OFF is specified, and no lines from the message buffer are printed to the CLP window.
CREATE PROCEDURE proc1( P1 VARCHAR(10) )
BEGIN
CALL DBMS_OUTPUT.PUT( 'P1 = ' );
CALL DBMS_OUTPUT.PUT_LINE( P1 );
END@
SET SERVEROUTPUT ON@
CALL proc1( '10' )@
SET SERVEROUTPUT OFF@
CALL proc1( '20' )@
The example results in the following output:
CALL proc1( '10' )
Return Status = 0
P1 = 10
SET SERVEROUTPUT OFF
DB20000I The SET SERVEROUTPUT command completed successfully.
CALL proc1( '20' )
Return Status = 0