When you run a script it is very useful to have the progress written out to the screen or a file. The easiest way to do this is via the DBMS_OUTPUT package. The only problem is if you like a lot of progress information you may sometimes come across the following error:
ORA-20000: ORU-10027: buffer overflow, limit of xxx bytes
From Oracle 10.2 it is easy to avoid this error (for earlier versions you often just had to reduce the information you were displaying). Before 10.2 the buffer size was limited to one million bytes with a default size of 20,000. This meant that you could only write out 20,000 characters before you got this message. What people started doing was to always put the maximum size (1,000,000) at the start of their scripts.
Now in Oracle 10.2 and up this limit has been removed although there are a couple of things you must be careful with which will be shown below.
With a couple of simple tests we can see how this works:
set serveroutput on size 10000
After 10000 characters:
ORA-20000: ORU-10027: buffer overflow, limit of 10000 bytes
set serveroutput on size 30000
After 30000 characters:
ORA-20000: ORU-10027: buffer overflow, limit of 30000 bytes
set serveroutput on size 1000000
After 1000000 characters:
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
set serveroutput on size 2000000
SP2-0547: size option 2000000 out of range (2000 through 1000000)
set serveroutput on
No error after 1000001 characters.
Note however this:
set serveroutput on size 2000
set serveroutput on
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
So if set serveroutput on is on its own in a script the buffer size will be set to “NULL” (ie unlimited), if however there is an earlier set serveroutput on size xx, the value xx will be used until specifically set to another value.
An alternative option is to call the procedure
Where xx is the buffer size (or xx is NULL to have unlimited buffer). Be aware that calling this with no parameter, ie:
DBMS_OUTPUT.ENABLE ();will set the buffer size to 20000. So make sure you pass the parameter NULL: DBMS_OUTPUT.ENABLE (NULL); if you want an unlimited buffer size.