Re: dbms_output (buffer overflow)

DAVID pOWER wrote:
>
> Nick Clark <NickC_at_dial.pipex.com> wrote:
> >Chrysalis wrote:
> >`
> >` Rami Juhela wrote:
> >` >
> >` > Is there any way to prevent buffer overflow when using
> >` > dbms_output.put_line in PL/SQL -cursor loop? (Oracle7).
> >` >
> >`
> >` I presume you are executing your PL/SQL from SQL*Plus.
> >` So you need to specify SET SERVEROUT[PUT] ON SIZE nnnnn
> >` (Note that the ON is required, even if it is already on!)
> >`
> >Or you can use
> >
> >DBMS_OUTPUT.ENABLE(100000);
> >
> >at the start of your script, which does the same thing ?
> >
> >Nick.
> >--
> I'm already setting the buffer to the maximum possible size and I still
> get the overflow problem. this occurrs as I'm using DBMS_OUT.PUT_LINE
> statements in a loop for reporting errors. Unfortunately, if every record
> I'm processing generates an error I overflow the buffer.
>
> Is there any way to flush or clear the buffer.
>
> Note: DBMS_OUTPUT.DISABLE is supposed to do this. But enabling and
> disabling DBMS_OUTPUT for every loop interation has not solved the
> problem.
>
> David Power

What I have done in the past is use DBMS_PIPE and stuff all my messages into a pipe, then have a loop in another session unpack the pipe and dbms_output the messages as they are occurring. If you are interested in a "debug" package that I wrote to handle this, send me an email and I'll send it back to you.

If I get much interest, I'll post it here. Or is there a better place? Maybe we should have a comp.databases.oracle.packages group?

-- 
Diana Duncan	     | My opinions are my own.
Sr. Consultant	     | 
REALOGIC, Inc.	     | Excitement, Adventure and
dduncan_at_realogic.com | Really Wild Things - Z.B.