dbms_output hard limit in SQL Developer

Question

I have an issue with SQL Developer - it is not possible to set dbms_output buffer larger than 1 000 000 symbols, while it works in SQL*Plus. This script:

SET SERVEROUTPUT ON SIZE UNLIMITED
 BEGIN
 for c in (SELECT rpad('a',1000,'a') ll from dual connect by level <=2001) loop
 dbms_output.put_line (c.ll);
 end loop; 
 dbms_output.put_line ('www');
 END;
 / 

works in SQL*Plus, but fails with:

ORA-20000: ORU-10027: buffer overflow, limit of 1000000 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

in SQL Developer.

I've tried to use dbms_output view in SQL Developer and set size to unlimited as described here, but the same amount of symbols is printed and there is no 'www' line. Version of SQL Developer is 4.03.16. Is it a known issue?

Answer 1

In SQL Developer, SET SERVEROUTPUT ON SIZE UNLIMITED sets the buffer to the maximum value 1000000 instead of unlimited (tested with 12.1.0.2.161018 database and SQL Developer 4.1.5.21). You can confirm this easily with SQL tracing.

As an alternative, the below really sets the the buffer to unlimited:

begin
 dbms_output.enable(null);
end;
/