Re: PL/SQL DBMS_OUTPUT buffer limit

[Quoted] Kjell Ove Skarsbø wrote:

> 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:
> 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?

[Quoted] [Quoted] Try this at the beginning of your procedure

BEGIN
   DBMS_OUTPUT.DISABLE;
   DBMS_OUTPUT.ENABLE(1000000); If that isn't large enough ... make the buffer larger.

Daniel Morgan Received on Tue Jun 04 2002 - 17:51:32 CEST