ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes

The default buffer sizes depend on the oracle version. In the above case the limit was 2000 bytes.

You can increase the buffer size up to 1,000,000.

Pl/SQL: DBMS_OUTPUT.ENABLE(100000)

SQL*Plus: set serveroutput on size 1000000

From Oracle 10g, it is possible to make the buffer size unlimited:

PL/SQL: DBMS_OUTPUT.ENABLE (buffer_size => NULL);

SQL*Plus: set serveroutput on size unlimited

set serveroutput on setting is now equivalent to set serveroutput on size unlimited

Example:

Problem:

set serveroutput on

BEGIN

FOR i IN 1 .. ((1000000/32767)+1) LOOP

DBMS_OUTPUT.PUT_LINE( RPAD('x',32767,'x') );

END LOOP;

END;

/

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 3

Solution:

SQL*Plus:

set serveroutput on

or

set serverout on size unlimited

BEGIN

FOR i IN 1 .. ((10000000/32767)+1) LOOP

DBMS_OUTPUT.PUT_LINE( RPAD('X',32760,'X') );

END LOOP;

END;

/

PL/SQL:

set serveroutput on

BEGIN

DBMS_OUTPUT.ENABLE (buffer_size => NULL);

FOR i IN 1 .. ((10000000/32767)+1) LOOP

DBMS_OUTPUT.PUT_LINE( RPAD('X',32760,'X') );

END LOOP;

END;

/

If the solution does not work please check your oracle version and the client support this features or not.