SET SERVEROUTPUT ON SIZE - only 1000000 bytes?

From: Miguel Nunes Pinto <j-miguel-pinto_at_telecom.pt>
Date: Mon, 14 Feb 2000 19:49:28 GMT
Message-ID: <38a853ce.202334631@news.telecom.pt>

Hy,

I need to spool de result of a query into a file. I'm using, in a PL/SQL block, a cursor for the query and a dbms_output.put_line to send the results to the file. In the beginning i just set this:

set serveroutput on;

Then the first error was this:

begin                                                     
*                                                         
ERROR at line 1:                                          
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at line 43                                     

In my manual i've found this:

---

SERVEROUT[PUT] {OFF|ON} [SIZE n] [FOR[MAT] {WRA[PPED]|WOR[D_WRAPPED]|   TRU[NCATED]}]     Controls whether to display the output (DBMS_OUTPUT.PUT_LINE) of     stored procedures or PL/SQL blocks in SQL*Plus. OFF suppresses

    the output of DBMS_OUTPUT.PUT_LINE; ON displays the output.       
                                                                      

    SIZE sets the number of bytes of the output buffered within the     Oracle7 Server. The default for n is 2000. n cannot be less than     2000 or greater than 1,000,000.

---

Then i set this
set serveroutput on size 1000000;

But isn't enough:

begin                                                        
*                                                            
ERROR at line 1:                                             
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: at line 43                                        

I estimate that the final file would be more like 22.500.000 bytes.

So, please, any suggestions i'll be greatly appreciated.

Thanks in advance.
Miguel Pinto

PS:
A sample of my program:

spool file_name.txt;                                   
set serveroutput on size 1000000;                  
begin                                              
declare cursor pont_dist is                       
	Select 
	... a very big one

pd pont_dist%rowtype;
...

    for pd in pont_dist loop

dbms_output.put_line(pd.npd||';'||pd.cen||';'||pd.tip||';'||pd.plt||';'||pd.cap||';'||pd.cab||';'||pd.disp||';'||pd.ocpp||';'||pd.pa2p||';'||pd.ress||';'||pd.numprj||';'||pd.ritprj||';'||pd.aprov||';'||pd.morada||';');

    end loop;
...

spool off; Received on Mon Feb 14 2000 - 13:49:28 CST