ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes

ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes

I kept getting following error while I was trying to run the Oracle Support suggested script:

ERROR at line 1: 
ORA-20000: ORU-10027: buffer overflow, limit of 2000 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 28 

I was suffering from short of time so instead of consulting the Oracle Support I googled it and found the following solution:

I opened the script and changed the following line

set serveroutput on 
with 
set serveroutput on size 1000000

 and then executed the script it executed nicely.

Oracle basically says this:

20000, 00000, "%s"

// *Cause:  The stored procedure 'raise_application_error'

//          was called which causes this error to be generated.

// *Action: Correct the problem as described in the error message or contact

//          the application administrator or DBA for more information.

It doesn't really help.

I finally found the answer to increase the output characters using:

DBMS_OUTPUT.ENABLE()

By default, you can output 2000 characters, the maximum is 1,000,000

DBMS_OUTPUT.ENABLE(1000000);

However, If the code excedes the limit you will get the same error.

You can also also use the following to fix the error message:

set serveroutput on size 1000000

Hope this helps.

Thanks,

following is the link from where I found the solution:

http://www.franklinfaces.com/Topic196-56-1.aspx

Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions