HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

dbms_output hard limit in SQL Developer

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sqllimitharddbms_outputdeveloper

Problem

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?

Solution

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;
/

Code Snippets

begin
  dbms_output.enable(null);
end;
/

Context

StackExchange Database Administrators Q#156767, answer score: 7

Revisions (0)

No revisions yet.