Toad World® Forums

dbms output buffer size or pipe to file

Greetings!

Problem: When I turn dbms server output on and run a query with loads of dbms_output.put_line statements I get an error telling me the dbms output buffer is too small. Can anyone tell me if I can increase the buffer size? These don’t seem to work:

–exec dbms_output.enable(999999999999999999999999999999999999999);
–SET SERVEROUTPUT ON size ‘999999999999999999999999999999999999999’;
–exec DBMS_OUTPUT.ENABLE(999999999999999999999999999999999999999);

It would also be great if I could pipe the output to a file instead of the output window. Is that possible?

Hi Amplus,

The buffer size was limited by Oracle, the maximum is 32512 in 10g and 32767 in 11g.

That means even if you set your buffer by "exec dbms_output.enable(999999999999999999999999999999999999999); " , you still can’t have byte number in dbms_output larger than 32512 in 10g and 32767 in 11g. otherwise, get error said buffer size too small. So I wonder how many bytes you want to drop into dbms_output? If it’s overflowed?

If you’d like, you can have a look into this website as reference:
http://www.oracle-developer.net/display.php?id=327

For your second issue, pipe the output to a file, we can do this for a query, but don’t support for dbms_output message. I wonder if right click option “Save to file” in output window can meet your need? Thx.

Regards,
Shirly

Please note, the limitation of 32767 bytes is for the line length, not the total output buffer size. Therefore your getting the errors means that you probably need to split your output into separate lines. In Oracle 11g, you can even set the buffer size to UNLIMITED (SQL Navigator has a preference to enable this automatically), but this still won’t work if your lines are too long.

To save your output to a file, you can also enable spool in Code Editor, and then save the contents of the Spool tab into a file.

Hope this helps,
Roman