DBMS_OUTPUT buffer size (client or database side)

Question

I use SQL Developer to run queries on Oracle databases, the DBMS_OUTPUT buffer size is set by default (20000) in SQL Developer.

When I run the query against DEV database, everything is OK. When I run the same query against Production database, the same SQL Developer session, I get error :

ORA-20000: ORU-10027: buffer overflow, limit of 10000 bytes.

Have you an idea why ? There is any limitation set on database side.

https://stackoverflow.com/questions/16476568/how-to-increase-dbms-output-buffer may be this will help you — D.J., Apr 26 '20 at 11:59
The buffer size is set to 20000, why my query is limited to 10000? — Morad, Apr 26 '20 at 12:04
DECLARE BEGIN dbms_output.enable(NULL); -- Disables the limit of DBMS -- Your print here ! END; try to disabling the limit, if still you have error then the client you are using i.e sql developer having the conflicts. — D.J., Apr 26 '20 at 12:08

score 0 · Answer 1 · answered Nov 23 '21 at 22:30

You didn't reveal how exactly you call procedures from dbms_output package so I can just guess. In fact I got same error and found a cause in my case which might be of course different than yours.

In my case I had a buffer of length 10000 (in PLSQL Developer, which is not important here) and I called only the dbms_output.put procedure, not dbms_output.put_line. The put procedure keeps filling buffer and does not flush it, even if string argument contains newline character.

Compare these two attempts of writing 1001 lines of ten-characters-long string (nine visible + line terminator):

begin
  dbms_output.enable(10000);
  for i in 1..1001 loop
    dbms_output.put('123456789' || chr(10)); -- fails here
  end loop;
end;

vs.

begin
  dbms_output.enable(10000);
  for i in 1..1001 loop
    dbms_output.put_line('123456789'); -- works
  end loop;
end;

The first example fails with ORA-20000: ORU-10027: buffer overflow, limit of 10000 bytes because it tries to put 10010 chars into buffer.

(The real story from which this minimalistic example was synthetized was printing package source code from dba_source table. Every line of source source code in text column is terminated by newline character which must be trimmed before sending into put_line - without trimming it would be doubled; sending text into sole put method would cause the buffer trouble.)

DBMS_OUTPUT buffer size (client or database side)

1 Answers1