Re: SQL_PLUS buffer overflow

From: Tim Cross <tcross_at_pobox.une.edu.au>
Date: 08 Jun 2002 12:31:03 +1000
Message-ID: <87wutatsbc.fsf@blind-bat.une.edu.au>

"s Lehaire" <s.lehaire_at_meilleuregestion.com> writes:

> Hi !
> I've got this message (in french because I'm french) in SQL_PLUS when there
> is to much DBMS_OUTPUT.PUT_LINE()
> So can I increase the buffer of sql_plus? I've to see a debug of my
> procedure.
>
> ERREUR à la ligne 1 :
> ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
> ORA-06512: à "SYS.DBMS_OUTPUT", ligne 34
> ORA-06512: à "SYS.DBMS_OUTPUT", ligne 118
> ORA-06512: à "SYS.DBMS_OUTPUT", ligne 81
> ORA-06512: à "MPDEV.PR_MY_PROCEDURE", ligne 20
> ORA-06512: à ligne 1
>

DBMS_OUTPUT has a maximum buffer size of 1000000 bytes (and a maximum line size of 255). While you can overcome the line limit easily enough with a wrapper package, the buffer limit cannot be increased passed 1mb - so if you believe your app will be needing to output more than 1Mb, either use utl_file and write the data to a file or write the data to a temporary table and then select the data from that table using an sql statement outside your plsql etc.

Tim Received on Fri Jun 07 2002 - 21:31:03 CDT