Superar la limitación de los N caracteres con el paquete DBMS_OUTPUT.PUT_LINE

 Si hablamos de versiones de de Oracle Database anteriores a 10g R2 

Oracle facilitó el paquete DBMS_OUTPUT para los desarrolladores de PL/SQL. La función PUT_LINE() es la característica más utilizada por los desarrolladores Oracle. PUT_LINE se usa para mostrar resultados en la pantalla o para mostrar resultados en el spool y sacarlo a un archivo. Pero esta función tiene un límite solo permite 255 caracteres y si te pasas se provoca una excepción PL/SQL.

declare
   v_str varchar2(1000); 
begin 
   for i in 1..275 
   loop 
      v_str := v_str || TO_CHAR(i); 
   end loop; 
   dbms_output.put_line( v_str);
end;
/

Si ejecutamos el código en un motor de base de datos anterior a Oracle 10g R2, nos devuelve el error: 

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

Para solventar este problema podremos crear un procedimiento almacenado que sobrescriba el comportamiento de esta función de tal manera que divida la información en paquetes de 255 caracteres y la una en la salida.

create or replace procedure rafa.custom_output(in_string in varchar2 )
is 
   out_string_in long default in_string; 
   str_len number; 
   loop_count number default 0; 
begin 
   str_len := length(out_string_in);
   while loop_count < str_len
   loop 
      dbms_output.put_line( substr( out_string_in, loop_count +1, 255 ) ); 
      loop_count := loop_count +255; 
   end loop; 
end;

Si ejecutamos este código en nuestro motor de versión anterior a Oracle 10g R2, no dará el error.

declare
   v_str varchar2(1000); 
begin 
   for i in 1..275 
   loop 
      v_str := v_str || TO_CHAR(i); 
   end loop; 
   rafa.custom_output( v_str);
end; 
/

Si hablamos de versiones de de Oracle Database posteriores a 10g R2 Oracle amplió el tamaño de los caracteres a 32,767 bytes (el mismo límite superior que tiene otro paquete UTL_FILE). Este código ya no da ningún error para versiones del motor 10g R2 y 11g.

set linesize 64
BEGIN
DBMS_OUTPUT.PUT_LINE( RPAD('x',255,'x') );
END;
/

Si ejecutamos este código en un motor Oracle 10g R2 o superior, dará el siguiente error:

BEGIN
DBMS_OUTPUT.PUT_LINE( RPAD('$',32767,'$') || 'incluso mas...' );
END;
/
BEGIN
*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 32767
bytes per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 91
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 2

SQL> set serveroutput on size unlimited 
SQL> show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

Para ver que la opción ilimitada funciona, intentaremos sacar una salida superior al límite del buffer para esta opción (1000000 bytes). El siguiente ejemplo mostrará 1048544 bytes.

SQL> set lines 100
 
SQL> BEGIN
  2     FOR i IN 1 .. ((1000000/32767)+1) LOOP
  3        DBMS_OUTPUT.PUT_LINE( RPAD('$',32767,'$') );
  4     END LOOP;
  5  END;
  6  /
 
PL/SQL procedure successfully completed.

Eso si si abusamos de este código saldrá este error entre otro, así que cuidado con los experimentos, amiguitos: 
ORA-04030: out of process memory when trying to allocate 4120 bytes.