In article <38a853ce.202334631_at_news.telecom.pt>,
j-miguel-pinto_at_telecom.pt (Miguel Nunes Pinto) wrote:
> 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;
>
The dbms_output routine was intended as a debugging tool and was not
really intended for report production. For reports you should look
into using the utl_file package that started coming with the database
with ver 7.2, if I remember right. It allows you to read or write text
files on the database server, but you have to set it up in the init.ora
file before using it {utl_file_dir). It can not be used to read or
write to an application server unless you resort to using nfs mounted
files. Its major limination is that it has a line length limit of 1K,
but I for reports which are usually 80 - 133 it should provide what you
need.
--
Mark D. Powell -- The only advice that counts is the advice that
you follow so follow your own advice --
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Feb 15 2000 - 00:00:00 CST
