dbms_output size buffer overflow

oracle plsql oracle10g sqlplus dbms-output
Arav picture Arav · Nov 8, 2011 · Viewed 15.4k times · Source

I tried to set the dbms_output size to unlimited inside a stored procedure.

But it gave me compilation errors. So I tried in the SQL*Plus prompt the below way. But still I get the buffer overflow error. How can I overcome this?

 set serveroutput on size unlimited;
 exec service_update;


ORA-20000: ORU-10027: buffer overflow, limit of 30000 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 "ARBOR.SERVICE_UPDATE", line 27
ORA-06512: at line 1

Answer

DCookie picture DCookie · Nov 8, 2011

In the procedure service_update, by any chance is there a call to

dbms_output.enable(30000);

This may override the first limit you set.

Related questions

Ugly formatting in SQL*Plus

It is really annoying that when I run a select command in SQL*Plus such as: SELECT * FROM books; The output is really badly formatted and unreadable (row cells are not in a row but separated by line breaks etc): …

Read More
How to execute an oracle stored procedure?

I am using oracle 10g express edition. It has a nice ui for db developers. But i am facing some problems executing stored procedures. Procedure: create or replace procedure temp_proc is begin DBMS_OUTPUT.PUT_LINE('Test'); end it …

Read More
How to use BOOLEAN type in SELECT statement

I have a PL/SQL function with BOOLEAN in parameter: function get_something(name in varchar2, ignore_notfound in boolean); This function is a part of 3rd party tool, I cannot change this. I would like to use this function …

Read More