6

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?

7

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.