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 ?

Answer 1

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).