PL/SQL DBMS_OUTPUT buffer limit

From: Kjell Ove Skarsbø <ks_at_protek.no>
Date: 4 Jun 2002 07:48:20 -0700
Message-ID: <815bc2a.0206040648.4cc8fd64_at_posting.google.com>

[Quoted] [Quoted] Running a script retrieving different information from schema. A [Quoted] [Quoted] Cursor is created and a loop runs through all it's instances. The [Quoted] [Quoted] 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;
[Quoted] [Quoted] 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:
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? Received on Tue Jun 04 2002 - 16:48:20 CEST