Re: SQLPLUS serveroutput buffer overflow

Sometimes it it feasible to create multiple loops and do a subset in each. The serveroutput buffer is dumped and cleard at the end of each procedure. e.g.

set serveroutput on size 1000000
spool &1
begin
  for i in (select blahi WHERE SUBSET1) 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;
/

set serveroutput on size 1000000
spool &1
begin
  for i in (select blahi WHERE SUBSET2) 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;
/

If your procedure is fairly short and you only need a few subsets this can be "not too bad."

Gene Hubert
Durham, NC

"Peter Laursen" <ptl_at_edbgruppen.dk> wrote in message news:<3d53b61a$0$13711$edfadb0f_at_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 Mon Aug 12 2002 - 10:03:09 CDT