Re: PL/SQL DBMS_OUTPUT buffer limit

In article <815bc2a.0206060151.56c5da2_at_posting.google.com>, ks_at_protek.no says...
>
>Thomas Kyte <tkyte_at_oracle.com> wrote in message
>news:<adlg1b0agc_at_drn.newsguy.com>...
>> 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:
>> >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
>
>In a perfect world maybe, but not here.

Maybe, maybe not

>The script, as explained in the first message requieres user input.

[Quoted] but sqlplus can deal with that as well.

>This input is a test-condition, also different data are retrieved from
>6-7 tables and a cursor is controlling it.

[Quoted] but the cursor doesn't need to.

>Also there are duplicates in database so additional testing is
>requiered inside the loop to retrieve the latest and correct
>instances.

[Quoted] and SQL is competent enough to do that...

>On top of this the user input decides the format of the output file,
>which can be one out of two.

[Quoted] all we appear to need then is two queries and you will execute either QUERY1 or QUERY2.
>So a simple select is wanted but not possible I'm afraid.

I believe:

select a.*,

       ( SELECT c.first_name || ';' || c.last_name
       FROM clients c
[Quoted]       WHERE c.id = sub_ref_id  ) client_name,
       decode( (SELECT COUNT(*) FROM addresses WHERE entity_id = sub_ref_id),
[Quoted]                1, (SELECT area_name ||';' || district_name||';'||zip || 
                              ';' || city_name
                     FROM addresses
           WHERE sub_ref_id = addresses.entity_id )),
                  (select area_name ||';' || district_name||';'||zip ||
                            ';' || city_name
                     from addresses
            where entity_id = sub_ref_id
              and end_date = 
                      to_date('31/12/2222 23:59:59', 'DD/MM/YYYY HH24:MI:SS') ) 
                                                                 client_address
[Quoted]    from ( SELECT client_ext_attrs.*,
                 (SELECT sc.phone_number
                    FROM subscriber_channels sc
                   WHERE sc.subscriber_id = client_ext_attrs.client_id ) 
[Quoted]                                                                       tel_p_nr,
                 ( SELECT DISTINCT clients.reference_id
                     FROM clients
[Quoted]                     WHERE clients.id = client_ext_attrs.client_id ) sub_ref_id,
                 ( SELECT c.first_name || ';' || c.last_name
                     FROM clients c
[Quoted]                     WHERE c.id = client_ext_attrs.client_id ) subscriber_name
            FROM client_ext_attrs
           WHERE attr_type = 'NP'
             AND active_status ='1'
[Quoted]              and client_id in (select id
                                 from clients
                                where reference_id <> '0'
                                and status = '9')
             and 1 = ( SELECT COUNT(*)
                         FROM client_ext_attrs b
                        WHERE b.client_id = client_ext_attrs.client_id
                AND attr_type = 'NP'
                AND active_status ='1'
                AND client_ext_attrs.value = 'n')
         ) a

[Quoted] is equivalent (once you pick off the proper columns in the final select and [Quoted] concatenate them together) to all of your procedural logic.

[Quoted] Likewise, you can do the same with the second query.

[Quoted] Then, just create two views -- view1 and view2.

[Quoted] Your script simply becomes:

[Quoted] select * from view&opt;

>However, the problem has been solved using UTL_FILE package. Off
>course the drawback is that the file has to be retrieved from server
>if running from a client.
>Finals script:
>
>SET serveroutput ON
>PROMPT
>PROMPT
>PROMPT This script takes OUT either
>PROMPT 1. Data OF ALL customers that DO NOT reserve against
>publication
>PROMPT 2. Changes that IS made during a period OF TIME you decide.
>PROMPT
>PROMPT Choose OF the two options described over:
>ACCEPT opt NUMBER PROMPT 'Ditt valg : ';
>PROMPT
>PROMPT How many days back ??....
>ACCEPT s_days NUMBER PROMPT 'Tast antall dager tilbake (dersom du
>valgte nr.1 trykk enter) : ';
>PROMPT
>PROMPT
>DECLARE
> tel_p_nr subscriber_channels.phone_number%TYPE;
> c_f_name clients.first_name%TYPE;
> c_l_name clients.last_name%TYPE;
> c_addr1 addresses.area_name%TYPE;
> c_addr2 addresses.district_name%TYPE;
> c_zip addresses.zip%TYPE;
> c_p_area addresses.city_name%TYPE;
> s_first_name clients.first_name%TYPE;
> s_last_name clients.last_name%TYPE;
> sub_ref_id clients.id%TYPE;
> status_value client_ext_attrs.value%TYPE;
> status CHAR(100);
> counter NUMBER;
> reserved NUMBER;
> TODAY DATE := SYSDATE;
> startup_date DATE;
> fileHandler UTL_FILE.FILE_TYPE;
>
>
> 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
> IF (('&opt') = 1) THEN
> fileHandler := UTL_FILE.FOPEN('/export/ORACLE/oradata/YOU/utl_file_dir',
>'all_subscriber.txt', 'w');
> DBMS_OUTPUT.put_line('Filen finnes på
>/export/ORACLE/oradata/YOU/utl_file_dir/all_subscriber.txt på you2');
> UTL_FILE.put_line(filehandler,'T');
> FOR x_ext_att_inst IN x_ext_att LOOP
>
> counter := 0;
> reserved := 0;
> --deciding if this suscriber is not reserved
> SELECT COUNT(*) INTO
> reserved FROM client_ext_attrs
> WHERE x_ext_att_inst.client_id = client_ext_attrs.client_id
> AND attr_type = 'NP'
> AND active_status ='1'
> AND client_ext_attrs.value = 'n';
>
> IF (reserved < 2 AND reserved > 0) THEN
>
> --fetching subscribers phone number
> SELECT sc.phone_number INTO tel_p_nr
> FROM subscriber_channels sc
> WHERE sc.subscriber_id = x_ext_att_inst.client_id;
> utl_file.put(filehandler,tel_p_nr);
> utl_file.put(filehandler,';');
>
>
> --fetching correct client_id for subscriber
> SELECT DISTINCT clients.reference_id
> INTO sub_ref_id FROM clients
> WHERE clients.id = x_ext_att_inst.client_id;
>
> --fetching client properties
> SELECT c.first_name,c.last_name
> INTO c_f_name, c_l_name
> FROM clients c
> WHERE sub_ref_id = c.id;
> utl_file.put(filehandler,c_f_name);
> utl_file.put(filehandler,';');
> utl_file.put(filehandler,c_l_name);
> utl_file.put(filehandler,';');
>
> --deciding duplication in addresses
> SELECT COUNT(*)
> INTO counter
> FROM addresses
> WHERE entity_id = sub_ref_id;
>
> IF (counter > 0 AND counter < 2) THEN
> SELECT area_name,district_name,zip,city_name
> INTO c_addr1, c_addr2, c_zip, c_p_area
> FROM addresses
> WHERE sub_ref_id = addresses.entity_id;
> ELSE
>
> SELECT area_name,district_name,zip,city_name
> INTO c_addr1, c_addr2, c_zip, c_p_area
> from addresses
> where entity_id = sub_ref_id
> and end_date = to_date('31/12/2222 23:59:59', 'DD/MM/YYYY
>HH24:MI:SS');
> END IF;
> utl_file.put(filehandler,c_addr1);
> utl_file.put(filehandler,';');
> utl_file.put(filehandler,c_addr2);
> utl_file.put(filehandler,';');
> utl_file.put(filehandler,c_zip);
> utl_file.put(filehandler,';');
> utl_file.put(filehandler,c_p_area);
> utl_file.put(filehandler,';');
>
> -- fetching subscribers first and last name
> SELECT c.first_name, c.last_name
> INTO s_first_name ,s_last_name
> FROM clients c WHERE
> c.id = x_ext_att_inst.client_id;
>
> utl_file.put(filehandler,s_first_name);
> utl_file.put(filehandler,';');
> utl_file.put(filehandler,s_last_name);
> utl_file.put(filehandler,';');
> UTL_FILE.new_line(filehandler,1);
>
>
> END IF;
> END LOOP;
> counter := 0;
> reserved := 0;
>
>
>
> ELSIF (('&opt') = 2) THEN
> fileHandler := UTL_FILE.FOPEN('/export/ORACLE/oradata/YOU/utl_file_dir',
>'changed_subscriber.txt', 'w');
> DBMS_OUTPUT.put_line('Filen finnes på
>/export/ORACLE/oradata/YOU/utl_file_dir/changed_subscriber.txt på
>you2');
> UTL_FILE.put_line(filehandler,'C');
> startup_date := today - '&s_days';
> DBMS_OUTPUT.put_line('Endringer siden '||TO_CHAR(startup_date)||'
>skrives til fil');
> FOR x_ext_att_inst IN x_ext_att LOOP
>
> counter := 0;
> reserved := 0;
>
> --deciding if this client has changed status this year
>
> SELECT COUNT(*) INTO
> reserved FROM client_ext_attrs
> WHERE x_ext_att_inst.client_id = client_ext_attrs.client_id
> AND attr_type = 'NP'
> AND active_status ='1'
> AND client_ext_attrs.start_date > startup_date;
>
> IF (reserved < 2 AND reserved > 0) THEN
> --fetching subscribers phone number
> SELECT sc.phone_number INTO tel_p_nr
> FROM subscriber_channels sc
> WHERE sc.subscriber_id = x_ext_att_inst.client_id;
> utl_file.put(filehandler,tel_p_nr);
> utl_file.put(filehandler,';');
>
> --fetching correct client_id for subscriber
> SELECT DISTINCT clients.reference_id
> INTO sub_ref_id FROM clients
> WHERE clients.id = x_ext_att_inst.client_id;
>
> --fetching client properties
> SELECT c.first_name,c.last_name
> INTO c_f_name, c_l_name
> FROM clients c
> WHERE sub_ref_id = c.id;
> utl_file.put(filehandler,c_f_name);
> utl_file.put(filehandler,';');
> utl_file.put(filehandler,c_l_name);
> utl_file.put(filehandler,';');
>
> --deciding duplication in addresses
> SELECT COUNT(*)
> INTO counter
> FROM addresses
> WHERE entity_id = sub_ref_id;
>
> IF (counter > 0 AND counter < 2) THEN
> SELECT area_name,district_name,zip,city_name
> INTO c_addr1, c_addr2, c_zip, c_p_area
> FROM addresses
> WHERE sub_ref_id = addresses.entity_id;
> ELSE
>
> SELECT area_name,district_name,zip,city_name
> INTO c_addr1, c_addr2, c_zip, c_p_area
> from addresses
> where entity_id = sub_ref_id
> and end_date = to_date('31/12/2222 23:59:59', 'DD/MM/YYYY
>HH24:MI:SS');
> END IF;
> utl_file.put(filehandler,c_addr1);
> utl_file.put(filehandler,';');
> utl_file.put(filehandler,c_addr2);
> utl_file.put(filehandler,';');
> utl_file.put(filehandler,c_zip);
> utl_file.put(filehandler,';');
> utl_file.put(filehandler,c_p_area);
> utl_file.put(filehandler,';');
>
>-- -- fetching subscribers first and last name
> SELECT c.first_name, c.last_name
> INTO s_first_name ,s_last_name
> FROM clients c WHERE
> c.id = x_ext_att_inst.client_id;
>
> utl_file.put(filehandler,s_first_name);
> utl_file.put(filehandler,';');
> utl_file.put(filehandler,s_last_name);
> utl_file.put(filehandler,';');
>
> --deciding subscriber status for reservation
> SELECT cea.value INTO status_value
> FROM client_ext_attrs cea WHERE
> x_ext_att_inst.client_id = cea.client_id
> AND attr_type = 'NP'
> AND active_status ='1';
>
> IF (status_value = 'n') THEN
> status := 'add';
> ELSIF (status_value = 'y') THEN
> status := 'remove';
> ELSE
> status := '';
> END IF;
> utl_file.put(filehandler,status);
> utl_file.put(filehandler,';');
>
>
> UTL_FILE.new_line(filehandler,1);
>
> END IF;
> END LOOP;
> counter := 0;
> reserved := 0;
>
>
>
> ELSE
> DBMS_OUTPUT.put_line('Valget må enten være 1,2 eller 3...........');
> END IF;
> UTL_FILE.FCLOSE(filehandler);
>EXCEPTION
> WHEN UTL_FILE.INVALID_PATH THEN
> RAISE_APPLICATION_ERROR(-20100,'Invalid Path');
>
> WHEN UTL_FILE.INVALID_MODE THEN
> RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');
>
> WHEN UTL_FILE.INVALID_FILEHANDLE THEN
> RAISE_APPLICATION_ERROR(-20102,'Invalid Filehandle');
>
> WHEN UTL_FILE.INVALID_OPERATION THEN
> RAISE_APPLICATION_ERROR(-20103,'Invalid Operation -- May signal a
>file locked by the OS');
>
> WHEN UTL_FILE.READ_ERROR THEN
> RAISE_APPLICATION_ERROR(-20104,'Read Error');
>
> WHEN UTL_FILE.WRITE_ERROR THEN
> RAISE_APPLICATION_ERROR(-20105,'Write Error');
>
> WHEN UTL_FILE.INTERNAL_ERROR THEN
> RAISE_APPLICATION_ERROR(-20106,'Internal Error');
>
> WHEN NO_DATA_FOUND THEN
> RAISE_APPLICATION_ERROR(-20107,'No Data Found');
>
> WHEN VALUE_ERROR THEN
> RAISE_APPLICATION_ERROR(-20108,'Value Error');
>
> WHEN OTHERS THEN
> RAISE_APPLICATION_ERROR(-20109,'Unknown UTL_FILE Error');
>END;
>/

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