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. 

Comments