ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line

Do you like the full featured supplied PL/SQL Package DBMS_OUTPUT? It seems to be the packgage which ranks in top position with it's limitations ;-)

• limited maximal output buffer
• limited length of line (255)
• limited support of output of datatypes like CLOB, XML, ...

Some points will be worked out with Oracle 10.2 - but why Oracle let it's users wait so long?
At the current project i developed a source code generator generating a table api with special demand for an OR-Mapper. For some easy usage i printed the code templates to output, strored as CLOB.
After 255 digits execeded i made this experience :

SET SERVEROUTPUT ON
DECLARE
l_str VARCHAR2(1024) := '01234567890123456789012345678901234567890123456789' ;
l_xl_str VARCHAR2(1024);
BEGIN
l_xl_str := l_str || chr(10) || l_str || chr(10) || l_str || chr(10) || l_str || chr(10) || l_str || chr(10);
  dbms_output.put_line('LENGTH : ' || length(l_xl_str));
  dbms_output.put_line(l_xl_str);

l_xl_str := l_xl_str || chr(10) || l_str;
  dbms_output.put_line('LENGTH : ' || length(l_xl_str));
  dbms_output.put_line(l_xl_str); 
END;
/

SQL>

LENGTH : 255
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789

LENGTH : 306

DECLARE
l_str VARCHAR2(1024) := '01234567890123456789012345678901234567890123456789' ;
l_xl_str VARCHAR2(1024);
BEGIN
l_xl_str := l_str || chr(10) || l_str || chr(10) || l_str || chr(10) || l_str || chr(10) || l_str || chr(10);
dbms_output.put_line('LENGTH : ' || length(l_xl_str));
dbms_output.put_line(l_xl_str);

l_xl_str := l_xl_str || chr(10) || l_str;
dbms_output.put_line('LENGTH : ' || length(l_xl_str));
dbms_output.put_line(l_xl_str);
END;

ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 133
ORA-06512: at line 11

Immediatly i searched for a workaround on the web - even on Tom Kytes AskTom site i did not found something which really helped. So i had to find my own workaround. The Procedure can print maximal 32K sized string due to the PL/SQL limitation. But it could be overloaded with CLOB datatype parameter and the use of DBMS_LOB package inside of the procedure.

DECLARE
l_Str    VARCHAR2(1024) := '01234567890123456789012345678901234567890123456789';
l_Xl_Str VARCHAR2(1024);

PROCEDURE Put_Xl_Line(p_Str IN VARCHAR2) IS
 l_Length PLS_INTEGER := 0;
 l_Offset PLS_INTEGER := 0;
 l_Cr_Pos PLS_INTEGER := 0;
 l_Line   VARCHAR2(256);
BEGIN

 l_Length := Length(p_Str);
 l_Offset := 1;

 WHILE l_Offset <= l_Length LOOP
   l_Cr_Pos := Instr(Substr(p_Str, l_Offset, 255),  Chr(10));
   -- if last line without CR 
   IF (l_Cr_Pos = 0) THEN
     l_Cr_Pos := l_Length - l_Offset + 2;
   END IF;
   l_Line := Substr(p_Str,  l_Offset,  l_Cr_Pos - 1);
   Dbms_Output.Put_Line(l_Line);
   l_Offset := l_Offset + l_Cr_Pos;
 END LOOP;
END Put_Xl_Line;

BEGIN
 l_Xl_Str := l_Str || Chr(10) || l_Str || Chr(10) || l_Str || Chr(10) ||
             l_Str || Chr(10) || l_Str;
 Dbms_Output.Put_Line('LENGTH : ' || Length(l_Xl_Str));
  Put_Xl_Line(l_Xl_Str);

 l_Xl_Str := l_Xl_Str || Chr(10) || l_Str;
 Dbms_Output.Put_Line('LENGTH : ' || Length(l_Xl_Str));
  Put_Xl_Line(l_Xl_Str);
END;
/ 

SQL>

LENGTH : 254
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
LENGTH : 305
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789

PL/SQL procedure successfully completed

Wow it really worked!!
Carl ;-)