Oracle: buffer overflow (ORU-10027)

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
i number ;
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 lang=”oracle8″]

set serveroutput on size 100000


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

set serveroutput on size unlimited


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 ;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.