DBMS_OUTPUT.PUT_LINE for long strings

From: Peter Mroz <junkpmroz_at_junkdomaincorp.com>
Date: 1997/08/21
Message-ID: <33FCA3A2.4616@junkdomaincorp.com>#1/1

I'm writing a PL/SQL code generator and am in the process of debugging it, so I need to print out code that has been generated. This entails printing out some rather long strings. Unfortunately dbms_output.put_line can only handle a maximum of 255 characters. Does anyone have a way to print longer strings? I wrote a procedure to do it but it gives me an extra line feed after every 'chunk'.

Thanks for any help.

Peter

Here's my procedure. I found that for best results SET LINESIZE 255.

put_line.sql:

---

create or replace package my is

PROCEDURE put_line(i_string IN VARCHAR2);

END my;

/

show errors;

create or replace package body my is

/* put_line: version of dbms_output.put_line that handles strings of
arbitrary length. Bytes off 255 character chunks at a time. Tries to break lines nicely
*/

PROCEDURE put_line(

        i_string IN VARCHAR2)
IS

    l_pname	CONSTANT VARCHAR2(30) := 'put_line';
    l_start	INTEGER;
    next_length	INTEGER;
    l_length	INTEGER;
    max_length	CONSTANT INTEGER := 255;
    line_feed	CONSTANT CHAR(1) := CHR(10);
    keep_going	BOOLEAN;
    err_msg	VARCHAR2(1);

BEGIN     IF (i_string IS NULL) THEN

        RETURN;
    END IF;     l_length := LENGTH(i_string);

    IF (l_length <= max_length) THEN

        DBMS_OUTPUT.PUT_LINE(i_string);
    ELSE
/* Pick the string apart in 255-byte chunks, at line feeds */

	l_start    := 1;
	keep_going := true;

	LOOP



/* See if we're at the end of the string or not */

	    IF ((l_start + max_length - 1) >= l_length) THEN
		keep_going  := false;
		next_length := l_length - l_start + 1;
	    ELSE

/* Look backwards from the end of the next 255-byte string for a line
feed */

		next_length := INSTR(SUBSTR(i_string, l_start, max_length), 
				 line_feed, -1);

/* If we didn't find ANY line feeds in this 255-character segment, just
print
out the whole thing */

		IF (next_length = 0) THEN
		    next_length := max_length;
		ELSE


/* Don't print out the last LF */

		    next_length := next_length - 1;
		END IF;

	    END IF;



/* Print out the substring we just found */

	    DBMS_OUTPUT.PUT_LINE(SUBSTR(i_string, l_start, next_length));

	    l_start := l_start + next_length;

	    IF (NOT keep_going) THEN
		EXIT;
	    END IF;

	END LOOP;

    END IF; EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Error in ' || l_pname || ': ' || sqlerrm);

END put_line;

END my;

/

show errors;

---

-- 
Peter Mroz				Domain Solutions Corporation
Tel: 610-892-7540			1023 East Baltimore Pike, Suite 205
Fax: 610-892-7616			Media, PA 19063

To get my correct email address just remove the words junk from my
reply-to address.  Isn't spam just lovely?

Received on Thu Aug 21 1997 - 00:00:00 CDT