Enabling DBMS_OUTPUT

Your primary All Database information source.

22 November 2009 855 views One Comment

DBMS_OUTPUT is a package that let you able to write some output on the screen. In SQL*Plus you can enable the output with this command:

SQL> SET serverout ON

All output will be written in a buffer. The default size of the buffer is 20,000 bytes. If you want a different buffer size you must use the enable procedure:

DBMS_OUTPUT.ENABLE (
   buffer_size IN INTEGER DEFAULT 20000);

The defualt buffer size is 20,000 bytes, the maximum size is 1,000,000 bytes and the minimum is 2,000. To enable an unlimited buffer size you must pass NULL to DBMS_OUTPUT.ENABLE. Here some examples:

-- wrong:
SQL> begin
      dbms_output.enable(10000000000000);
      FOR i IN 1..1000000 loop
        dbms_output.put_line(i);
      end loop;
    end;
    /
begin
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, LIMIT of 1000000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 4

-- wrong:
SQL> begin
      dbms_output.enable();
      FOR i IN 1..1000000 loop
        dbms_output.put_line(i);
      end loop;
    end;
    /
begin
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, LIMIT of 20000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 4

-- ok:
SQL> begin
      dbms_output.enable(NULL);
      FOR i IN 1..1000000 loop
        dbms_output.put_line(i);
      end loop;
    end;
    /
 
PL/SQL procedure successfully completed.