Carl's Oracle

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

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

Some points will be worked out with Oracle 10.2 - but why Oracle let its 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 the code templates are printed to output, stored as CLOB. After 255 digits line buffer exceeds i made this experience :

SET SERVEROUTPUT ON

DECLARE
  l_Str    VARCHAR2(1024) := '01234567890123456789012345678901234567890123456789';
  l_Xl_Str VARCHAR2(1024);
BEGIN
  -- constructing STRING WITH 255 chars
  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);

  -- constructing STRING WITH 255 +  chars
  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;
/

Output :

LENGTH : 255

01234567890123456789012345678901234567890123456789

01234567890123456789012345678901234567890123456789

01234567890123456789012345678901234567890123456789

01234567890123456789012345678901234567890123456789

01234567890123456789012345678901234567890123456789

LENGTH : 306

DECLARE
  l_Str    VARCHAR2(1024) := '01234567890123456789012345678901234567890123456789';
  l_Xl_Str VARCHAR2(1024);
BEGIN
  -- constructing STRING WITH 255 chars
  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);

  -- constructing STRING WITH 255 +  chars
  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

 

Immediately 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 (with maximal 255 chars per line) due to the PL/SQL limitation. But it could be overloaded with CLOB data type parameter and the use of DBMS_LOB package inside of the procedure Put_Xl_Line :

  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 &Lt ; = 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;
/

 Output :

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 ;-)

UPDATE : the procedure is able to 'print' large amount of generated code (32Kb) - but one code line may still not exceed 255 chars!
Carl; 19.11.2005