DB2 Version 9.7 for Linux, UNIX, and Windows

GET_LINE procedure - Get a line from the message buffer

The GET_LINE procedure gets a line of text from the message buffer. The text must be terminated by an end-of-line character sequence.

Tip: To add an end-of-line character sequence to the message buffer, use the PUT_LINE procedure, or, after a series of calls to the PUT procedure, use the NEW_LINE procedure.

Syntax


>>-DBMS_OUTPUT.GET_LINE--(--line--,--status--)-----------------><

Procedure parameters

line

An output argument of type VARCHAR(32672) that returns a line of text from the message buffer.

status

An output argument of type INTEGER that indicates whether a line was returned from the message buffer:

0 indicates that a line was returned
1 indicates that there was no line to return

Authorization

EXECUTE privilege on the DBMS_OUTPUT module.

Example

Use the GET_LINE procedure to get a line of text from the message buffer. In this example, proc1 puts a line of text in the message buffer. proc3 gets the text from the message buffer and inserts it into a table named messages. proc2 then runs, but because the message buffer is disabled, no text is added to the message buffer. When the select statement runs, it returns only the text added by proc1.

CALL DBMS_OUTPUT.ENABLE( NULL )@

CREATE PROCEDURE proc1()
BEGIN
  CALL DBMS_OUTPUT.PUT_LINE( 'PROC1 put this line in the message buffer.' );
END@

CREATE PROCEDURE proc2()
BEGIN
  CALL DBMS_OUTPUT.PUT_LINE( 'PROC2 put this line in the message buffer.' );
END@

CREATE TABLE messages ( msg VARCHAR(100) )@

CREATE PROCEDURE proc3()
BEGIN
  DECLARE line VARCHAR(32672);
  DECLARE status INT;

  CALL DBMS_OUTPUT.GET_LINE( line, status );
  while status = 0 do
    INSERT INTO messages VALUES ( line );
    CALL DBMS_OUTPUT.GET_LINE( line, status );
  end while;
END@

CALL proc1@

CALL proc3@

CALL DBMS_OUTPUT.DISABLE@

CALL proc2@

CALL proc3@

SELECT * FROM messages@

This example results in the following output:

MSG                                           
----------------------------------------------
PROC1 put this line in the message buffer.    

  1 record(s) selected.