Re: Line overflow - dbms_output.put

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 12 Nov 1999 07:42:39 -0500
Message-ID: <wgosOGFDvl5RRYT0dVdTY1OAmBg7@4ax.com>

A copy of this was sent to "Chua Boon Yiang" <chuaby_at_hotmail.com> (if that email address didn't require changing) On Fri, 12 Nov 1999 14:20:59 +0800, you wrote:

>Hi
>i am trying to
>dbms_output.put_line a very long line. but i got this error message
>when i execute the PL/SQL in SQL*Plus.
>
>ERROR at line 1:
>ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
>ORA-06512: at "SYS.DBMS_OUTPUT", line 99
>ORA-06512: at "SYS.DBMS_OUTPUT", line 65
>ORA-06512: at line 12
>
>i have tried
>
>SET SERVEROUTPUT ON FORMAT WORD_WRAPPED
>SET LINESIZE 2000
>set lines 5000
>set pages 5000
>
>But i still get the error.
>
>May i know how can i extend the length of line inorder print out a very long
>string of varchar2(2000).
>
>Thanks
>Boon Yiang
>

No, dbms_output will only print 255 characters/line. If you have a longer line, you must break it onto separate lines with linefeeds. I use a small routine P to do this, for example:

create or replace procedure p(p_str in varchar2) as

l_str long default p_str;
begin

	loop
		exit when l_str is null;
		dbms_output.put_line( substr( l_str, 1, 250 ) );
		l_str := substr( l_str, 251 );
	end loop;

end;

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Nov 12 1999 - 06:42:39 CST

Re: Line overflow - dbms_output.put_line