DB2 10.5 for Linux, UNIX, and Windows

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.

Table 1. Built-in routines available in the DBMS_OUTPUT module
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