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

[SashaBuilder3]

Hi everybody,

In one of my procedures I output some info for a debugging purpose. Here is the line:

DBMS_OUTPUT.put_line(my_msg);

'my_msg' is of VARCHAR2 type, it is assigned not more than 6 bytes at a time.

The line is used in a loop about 350 iterations. When I run the proc in SQL*PlusWorksheet I get at some point (around 100 iterations) an error message:

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


As I said this line is for debugging only and if I comment it out the whole procedure works OK.

Can anyone tell me what's going on?


Thanks,

Alexandre

 

[lmorgansky]

Each time you DBMS_OUTPUT, the text is written to a buffer. The buffer is not flushed until the end of the stored proc. Since the default since of the buffer is 2000, you reach that limit at about 350 iterations (6 bytes * 350 = 2100 bytes).

You can up the 2000 limit by doing a dbms_output.enable(<size here>). But there used to be a max limit of 1MB. Don't know if this is still true.

Lonnie

 

[SashaBuilder3]

Thanks Lonnie,

I see. Is there a way to flush it programmatically?


Alexandre

 

[karluk]

Lmorgansky is right, this is a problem with the size of the output buffer. The maximum size is 1,000,000 bytes, but you are comfortably under that limit. The real problem is that the &quot;set serveroutput on&quot; command has a default buffer size of 2,000. That's the limit you are over.

I think Lmorgansky's suggestion to use dbms_output.enable will work, but the easiest way around this problem is to specify an explicit buffer size as part of your &quot;set serveroutput&quot; command. For example

set serveroutput on size 1000000

 

[SashaBuilder3]

Thanks Karluk,


You are right, 1 Mb is quite sufficient.

Alexandre

 

[sem]

Another way is to READ and thus CLEAN the buffer by calling DBMS_OUTPUT.GET_LINE(s).

I think that the key of your problem is not in a small buffer size, but rather in that NOBODY reads it (as sql*plus does automatically), so READING buffer or DISABLING output at all are more preferable ways.

 

[SashaBuilder3]

Thanks Sem,

Now I see how to flush the buffer on the run.

Alexandre

 

[CharlieMike73]

Might I add a simple solution to your buffer issue...

if you are working within a loop that will run over and over again, simply use this...

<start of loop>
-- Clear the buffer, by turning it off
DBMS_OUTPUT.DISABLE;

-- Start the buffer up again.
DBMS_OUTPUT.ENABLE;

--- Now the buffer is empty and ready to recieve

<rest of your looped code...>

Hope this helps!

 

[SashaBuilder3]

Intelliweb,

It's a great tip, thanks a lot!


Alexandre