Re: PL/SQL DBMS_OUTPUT buffer limit

In article <815bc2a.0206040648.4cc8fd64_at_posting.google.com>, ks_at_protek.no says...
>
>Running a script retrieving different information from schema. A
>Cursor is created and a loop runs through all it's instances. The
>DBMS_OUTPUT.put_line is within this loop.
>--fragment of syntax
>SET serveroutput ON format wrapped SIZE 1000000
>PROMPT Resultatet spooles til c:\res_against_publ.txt
>SET TERMOUT OFF
>clear buffer
>declare
> CURSOR x_ext_att IS
> SELECT * FROM client_ext_attrs
> WHERE attr_type = 'NP'
> AND active_status ='1'
> and client_id in
> (select id from clients
> where reference_id <> '0'
> and status = '9');
>
>begin
> FOR x_ext_att_inst IN x_ext_att LOOP
> --spooling output in correct format
>DBMS_OUTPUT.put_line(tel_p_nr||';'||c_f_name||';'||c_l_name||';'||c_addr1||';'||c_addr2||';'||c_zip||';'||c_p_area||';'||s_first_name||';'||s_last_name);
>end;
>/
>set termout on;
>This fetces 11950 lines from database and returns the error msg:
>DECLARE
>*
>ERROR at line 1:
>ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
>ORA-06512: at "SYS.DBMS_OUTPUT", line 106
>ORA-06512: at "SYS.DBMS_OUTPUT", line 65
>ORA-06512: at line 92
>
>So, the question: [Quoted]
>I know that this buffer size is the upper limit.
>Are there anyway to workaround this problem?
>The script can not be changed to a simple statment, though user input
>is requested.
>Anyone?

that is the upper limit.

only workarounds involve not using dbms_output.

why cannot this seemingly very simple query be executed standalone?

set heading off
set trimspool on
set feedback off
set embedded on
spool foo
select field||';'||field||';'||field....   from ....
spool off

that would seem to work pretty well

--
Thomas Kyte (tkyte_at_oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
[Quoted] Opinions are mine and do not necessarily reflect those of Oracle Corp