ORA-20000 -- Buffer Overflow

Often when we use dbms_output.putline in our sql's to print on the console after setting serveroutput on, it throws an error saying buffer overflow.

ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 198
ORA-06512: at "SYS.DBMS_OUTPUT", line 139
ORA-06512: at line 9

Option to get rid of this issue are,

1. SET SERVEROUTPUT ON SIZE 100000000
You might get an error on 9i as below,
SP2-0547: size option 276447232 out of range (2000 through 1000000)
In 10g you can specify UNLIMITED for SIZE
SET SERVEROUTPUT ON SIZE UNLIMITED

2. exec DBMS_OUTPUT.ENABLE(10000000);
This works for any value on 9i and it accepts UNLIMITED on 10gR2.

Else the other option is to use UTL_FILE to flush the output to a file.

Pre-requisite to use UTL_FILE is to create a database directory.

CREATE DIRECTORY 'DIRNAME' AS 'PATH' '';

Path should already be added to UTL_FILE_DIR or add it once you create the directory.

Refer to link http://www.psoug.org/reference/utl_file.html for syntax and options.

Following is an example script,

SQL> CREATE DIRECTORY MI AS '/tmp/';
SQL> DECLARE
  mith UTL_FILE.FILE_TYPE;
BEGIN
  mith := UTL_FILE.FOPEN(MI, 'test1.txt', 'w');
  UTL_FILE.PUTF(mith, 'Finally writing to a file!!!')
  UTL_FILE.FCLOSE(mith);
EXCEPTION
  WHEN utl_file.invalid_path THEN
     raise_application_error(-20000, 'Invalid path. Create directory or set UTL_FILE_DIR.');
END;
SQL>