Sometimes it’s necessary to show the output of the PL/SQL script.
Usually the dbms_output package is used:

[cc lang=”oracle8″]
set serveroutput on
declare
i number ;
begin
i:=0 ;
while i< 100000 loop i:=i+1 ; dbms_output.put_line('This is just test line #' || to_char(i) ) ; end loop ; end; / [/cc] After several thousands lines the following error message is displayed:

ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes
ORA-06512: at “SYS.DBMS_OUTPUT”, line 32
ORA-06512: at “SYS.DBMS_OUTPUT”, line 97
ORA-06512: at “SYS.DBMS_OUTPUT”, line 112
ORA-06512: at line 8

The error message means, that the internal buffer of dbms_output is full. How to increase it?
The following methods could be used:

[cc lang=”oracle8″]
…
dbms_package.enable(100000) ;
…
[/cc]

[cc lang=”oracle8″]
…
set serveroutput on size 100000
…
[/cc]

In Oracle 10gR2 the new addition “size unlimited” could be used:
[cc lang=”oracle8″]
…
set serveroutput on size unlimited
…
[/cc]

If these methods do not work, the temporary table could be used:
[cc lang=”oracle8″]
create sequence my_output_seq ;
create global temporary table my_output_table( my_row number, my_out varchar2(120) ) ;
…
insert into my_output_table values ( my_output_seq.nextval, ‘This is some string’ ) ;
…
select my_out from my_output_table order by my_row ;
drop table my_output_table ;
drop sequence my_output_seq ;
[/cc]