DBMS_OUTPUT Buffer: Set serveroutput on size

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
DBMS_OUTPUT.ENABLE (xx);
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.

About DBAdmin

Most of what I post here will be specific to the particular system I am currently working on. When I have time I will try to put some generic information too.
This entry was posted in Oracle 10.2, Oracle 11g, Tips. Bookmark the permalink.

3 Responses to DBMS_OUTPUT Buffer: Set serveroutput on size

  1. Wayne Hansen says:

    How do I use these two lines in a package?
    set serveroutput on size 1000000
    exec dbms_output.enable(‘10000000000′);

    • SET SERVEROUTPUT ON is a SQLPlus command it cannot be used in PL/SQL.
      Its effect is to set DBMS_OUTPUT.ENABLE () and when a PL/SQL call returns to SQLPlus the contents of the output buffer are dumped to the screen.

      Here is an example:

      CREATE OR REPLACE PROCEDURE MYTEST AS
      BEGIN
      	DBMS_OUTPUT.ENABLE(null);
              DBMS_OUTPUT.PUT_LINE('Hello World');
      END;
      /
      SQL> EXEC MYTEST;
      
      PL/SQL procedure successfully completed.

      You will see there is no output. The message “Hello World” is still in the output buffer as “Serveroutput” is set to off.
      See what happens when we turn it on (first we will run the procedure again and you will see that both lines are returned!)

      SQL> SHOW SERVEROUTPUT
      serveroutput OFF
      SQL> SET SERVEROUTPUT ON
      SQL> EXEC MYTEST;
      Hello World
      Hello World
      
      PL/SQL procedure successfully completed.
      
      SQL> EXEC MYTEST;
      Hello World
      
      PL/SQL procedure successfully completed.
      
      SQL> SET SERVEROUTPUT OFF
      SQL> EXEC MYTEST;
      
      PL/SQL procedure successfully completed.
      
      SQL> EXEC MYTEST;
      
      PL/SQL procedure successfully completed.
      
      SQL> EXEC MYTEST;
      
      PL/SQL procedure successfully completed.
      
      SQL> SET SERVEROUTPUT ON
      SQL> EXEC NULL;
      Hello World
      Hello World
      Hello World
      
      PL/SQL procedure successfully completed.

      Hope that explains things.

  2. Illusionist says:

    That’s really great!!.. U have explained the things so well that anyone who reads your answer, will understand easily. Thanks alot!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s