patternMinor
dbms_output hard limit in SQL Developer
Viewed 0 times
sqllimitharddbms_outputdeveloper
Problem
I have an issue with SQL Developer - it is not possible to set
This script:
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 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,
As an alternative, the below really sets the the buffer to unlimited:
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.