How to increase dbms_output buffer?

sql oracle plsql oracle10g dbms-output
hsuk picture hsuk · May 10, 2013 · Viewed 128.9k times · Source

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

Gopesh Sharma picture Gopesh Sharma · May 10, 2013

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

Related questions

How to use BOOLEAN type in SELECT statement

I have a PL/SQL function with BOOLEAN in parameter: function get_something(name in varchar2, ignore_notfound in boolean); This function is a part of 3rd party tool, I cannot change this. I would like to use this function …

Read More
Is it possible to output a SELECT statement from a PL/SQL block?

How can I get a PL/SQL block to output the results of a SELECT statement the same way as if I had done a plain SELECT? For example how to do a SELECT like: SELECT foo, bar FROM foobar; …

Read More
Oracle - Insert New Row with Auto Incremental ID

I have a workqueue table that has a workid column. The workID column has values that increment automatically. Is there a way I can run a query in the backend to insert a new row and have the workID column …

Read More