How to increase dbms_output buffer?

Question

I tried to debug my dynamic query via dbms_output but seems like the query string is too long for dbms_output buffer.

I got :

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_OUTPUT", line 148
ORA-06512: at line 1

Any idea how to increase the buffer size ?

You are probably writing way too much information using dbms_output. It is not designed for that. Your best option is probably to insert those messages into a table and then select from the table afterwards — , May 10 '13 at 07:40
@a_horse_with_no_name: I've never favored using a database table as an application log because of potentially unloggable incidents: for example, if an application can't connect to the database where do you log the fact that the app can't connect if the log is in the database you can't connect to? I prefer writing logs to flat files (using the UTL_FILE package in PL/SQL) which IMO is less likely to be unusable than is a table in a database. YMMV. Share and enjoy. — Bob Jarvis - Слава Україні, May 10 '13 at 12:59
@BobJarvis: inside a PL/SQL procedure I think this makes perfect sense. For regular application code (Java, C#,...) I do agree with you. — , May 10 '13 at 13:06

Gopesh Sharma · Accepted Answer · 2013-05-10T09:01:05.000

46

You can Enable DBMS_OUTPUT and set the buffer size. The buffer size can be between 1 and 1,000,000.

dbms_output.enable(buffer_size IN INTEGER DEFAULT 20000);
exec dbms_output.enable(1000000);

Check this

EDIT

As per the comment posted by Frank and Mat, you can also enable it with Null

exec dbms_output.enable(NULL);

buffer_size : Upper limit, in bytes, the amount of buffered information. Setting buffer_size to NULL specifies that there should be no limit. The maximum size is 1,000,000, and the minimum is 2,000 when the user specifies buffer_size (NOT NULL).

edited May 10 '13 at 09:01

answered May 10 '13 at 06:47

Gopesh Sharma

6,730
4
25
35

try disabling the dbms_output and then enable it with maximum size – Gopesh Sharma May 10 '13 at 07:12
6

You can also call dbms_output.enable(NULL), which will set the buffer size to unlimited, see http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_output.htm#i999293 – Frank Schmitt May 10 '13 at 07:32
@FrankSchmitt:`NULL` means "default" which is 20000. And there is no "unlimited" either. The maximim is 1000000 bytes. – May 10 '13 at 07:38
2

@a_horse_with_no_name: I think that changed in 10g. " The default buffer size is 20000 bytes. The minimum size is 2000 bytes and the maximum is unlimited." http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_output.htm – Mat May 10 '13 at 08:19
1

@a_horse_with_no_name Sorry, but that is not correct. See Mat's comment, the Oracle docs state: "buffer_size: Upper limit, in bytes, the amount of buffered information. Setting buffer_size to NULL specifies that there should be no limit." So giving NULL as argument to ENABLE is not the same as omitting the parameter entirely (since the default values is 20000). – Frank Schmitt May 10 '13 at 08:45
@FrankSchmitt: But the documentation also says: "*The maximum size is 1,000,000*" – May 10 '13 at 08:51
1

@a_horse_with_no_name The documentation says that *"The maximum size is 1,000,000, and the minimum is 2,000 when the user specifies buffer_size (NOT NULL)."* and *"Setting buffer_size to NULL specifies that there should be no limit."* – Gopesh Sharma May 10 '13 at 08:55
2

@a_horse_with_no_name Just tried it (10.2.0.4) - setting it to NULL allows me to print more than 1,000,000: set serveroutput on declare i pls_integer; buf varchar2(1000) default lpad('a', 1000, 'a'); begin dbms_output.enable(null); -- omitting the param leads to a buffer overflow begin for i in 1 .. 1001 loop dbms_output.put_line(i); dbms_output.put_line(buf); end loop; exception when others then dbms_output.put_line(SQLERRM); end; end; – Frank Schmitt May 10 '13 at 09:05
Using "unlimited" DBMS memory to log output makes me nervous. Flat files are our friends... :-) – Bob Jarvis - Слава Україні May 10 '13 at 13:09
1

does this works only for the current transaction? In another words: Do we need to set old value back? – Rodrigo Gurgel Jan 27 '15 at 18:17

score 4 · Answer 2 · edited May 23 '17 at 12:09

4

When buffer size gets full. There are several options you can try:

1) Increase the size of the DBMS_OUTPUT buffer to 1,000,000

2) Try filtering the data written to the buffer - possibly there is a loop that writes to DBMS_OUTPUT and you do not need this data.

3) Call ENABLE at various checkpoints within your code. Each call will clear the buffer.

DBMS_OUTPUT.ENABLE(NULL) will default to 20000 for backwards compatibility Oracle documentation on dbms_output

You can also create your custom output display.something like below snippets

create or replace procedure cust_output(input_string in varchar2 )
is 

   out_string_in long default in_string; 
   string_lenth number; 
   loop_count number default 0; 

begin 

   str_len := length(out_string_in);

   while loop_count < str_len
   loop 
      dbms_output.put_line( substr( out_string_in, loop_count +1, 255 ) ); 
      loop_count := loop_count +255; 
   end loop; 
end;

Link -Ref :Alternative to dbms_output.putline @ By: Alexander

edited May 23 '17 at 12:09

Community

1
1

answered May 10 '13 at 07:09

popats.

26
5

4

DBMS_OUTPUT.ENABLE(NULL) is not the same as omitting the parameter. The Oracle documentation (see the link) clearly states: "buffer_size Upper limit, in bytes, the amount of buffered information. Setting buffer_size to NULL specifies that there should be no limit." – Frank Schmitt May 10 '13 at 08:44
I think you need to change procedure with this : `create or replace procedure FRE_OUTPUT_REPLACE(input_string in varchar2 ) is out_string_in long default input_string; string_lenth number; loop_count number default 0; begin string_lenth := length(out_string_in); while loop_count < string_lenth loop dbms_output.put_line( substr( out_string_in, loop_count +1, 255 ) ); loop_count := loop_count +255; end loop; end FRE_OUTPUT_REPLACE;` – uzay95 Apr 05 '14 at 20:00
small mistake: string_lenth and str_len (should pick one of the names) :-) – Ulrik Larsen Jun 24 '22 at 08:21

Деян Добромиров · Answer 3 · 2016-08-24T08:06:58.763

4

Here you go:

DECLARE
BEGIN
  dbms_output.enable(NULL); -- Disables the limit of DBMS
  -- Your print here !
END;

edited Aug 24 '16 at 08:06

answered Mar 26 '15 at 13:04

Деян Добромиров

833
1
10
22

True, but he has to call it with NULL, because it written such in the documentation. buffer_size The upper limit, in bytes, the amount of buffered information. Setting buffer_size to NULL specifies that there should be no limit. – Деян Добромиров Nov 07 '19 at 10:59
but will there really be a limit of 20000? – Alex78191 Nov 08 '19 at 13:14
Nope. I just printed a hundred lines a `1000` characters each for a total of `100000`. Via [PL/SQL Developer](https://en.wikipedia.org/wiki/PL/SQL_Developer). Adjusting `dbms_output.enable(100)` will raise a buffer overflow error ;) – Деян Добромиров Nov 11 '19 at 09:11
So what means "DBMS_OUTPUT.ENABLE(NULL) will default to 20000 for backwards compatibility"? – Alex78191 Nov 11 '19 at 12:44
Probably if you run the code written with the new documentation in mind on an older version of oracle which has that limit in DBMS ;) – Деян Добромиров Nov 11 '19 at 12:52

How to increase dbms_output buffer?

3 Answers3

Linked