SQLPLUS serveroutput buffer overflow

From: Peter Laursen <ptl_at_edbgruppen.dk>
Date: Fri, 9 Aug 2002 14:31:32 +0200
Message-ID: <3d53b61a$0$13711$edfadb0f@dspool01.news.tele.dk>

SYSTEM 8.05 and up, WinNT 4 and W2k.

I made a sqlplus script that selects som data and spools them to a commaseparated file. The file exceeds the maximum size of the serveroutput var of 1000000 so the script terminates with ORU-10027 buffer overflow limit of 1000000
How can sove this? Can I empty the buffer during the run? If I set serveroutput off nothing is spooled out and I cannnot use PROMPT in the pl/sql block

the script takes a few parameters and then goes like:

set serveroutput on size 1000000
spool &1
begin
  for i in (select blah) loop
    dbms_output.put_line(i.master, i.blah)     for j in (select blah)
      dbms_output.put_line(j.detail lines);     end loop;
  end loop;
end;
/

TIA
Peter Laursen Received on Fri Aug 09 2002 - 07:31:32 CDT