Re: -10027: buffer overflow, limit of 1000000 bytes

cowlesd_at_my-deja.com wrote:
>I have a procedure which constantly generates the message in the
>subject line. I don't have serveroutput on, which is why I am
 confused.
>I don't quite see how it can argue about the buffer if the
 serveroutput
>is turned off..
>The code does have this in it:
>dbms_output.enable(1000000);
>which is what I usually use if I'm debugging something and need
 more
>output that 2000 bytes, but ususally it only seems to make a
 difference
>if I set serveroutput on..
>If I do a show serveroutput,
>I get OFF..
>We're getting rid of the dbms_output stuff as a workaround for
 now,but
>can someone explain this? If it is filling up the buffer
 anyhow, I'm
>confused since ususally if you don't have serveroutput on, it
 doesn't
>seem to really matter how much dbms_output you throw in your
 code..
>what am I missing?
>Thanks,
>Dc.
>

If you are running the code from sqlplus then you probably should not be using the dbms_output.enable procedure because it is really there to support imbedded pl/sql from pro*c or other languages. The purpose of the enable procure is to allocate the dbms_output buffer. I believe what is happening is once allocated by the enable procedure the output of dbms_output messages in written to the buffer even if you suppress display of the buffer in sqlplus because it was intended that the program that issued the enable would read the buffer using get_line.

If you are running in sqlplus and the procedure is not executed via a program then use 'set serveroutput on size 1000000' to allocate your buffer and delete the dbms_output.enable line. Also since the buffer comes out of the shared pool you are wasting resources to allocate more buffer than you need if you always use 1M.

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Mon Jul 24 2000 - 00:00:00 CDT