Working with the message buffer in stored procedures

DB2 9.7 introduced the module DBMS_OUTPUT, which provides a set of procedures that can be used to interact with the message buffer. There are many situations when we want to print something on screen from the procedure. This is very common when we are debugging a procedure. In this recipe, we will discuss different procedures available in the DBMS_OUTPUT module.

Getting ready

We need the EXECUTE privilege on the DBMS_OUTPUT module.

How to do it…

The DBMS_OUTPUT module provides a set of procedures that can be used as appropriate. All these procedures allow us to use the message buffer.

1. 1. Before we can use any of the available procedures, we need to decide the way in which we want to handle the message buffer. The most common use would be to display the contents on the standard output. We can use the following commands to redirect the message buffer to the standard output:

   SET SERVEROUTPUT ON;
   SET SERVEROUTPUT OFF;
   
   

   • When set to ON, the message...