Re: PL/SQL DBMS_OUTPUT buffer limit

[Quoted] In article <LCNL8.4769$nn1.1631693_at_news1.news.adelphia.net>, "Scott says...
>
>Oracle provides such limited debugging help!

[Quoted] [Quoted] huh? what would that have to do with anything? In any case, there are source [Quoted] code level debuggers (many) available for plsql. My preference is a simple [Quoted] debug routine that works just like your forms one but used UTL_FILE on the server.

>
>For Forms I wrote a proc that opens a file on the client side and writes
>what was passed to it and then closes the file. Then I can read the file
>with an editor to see in real time what is going on without losing any lines
>due to buffer overflow.
>
>I read about, but can not remember the term for it, a way to specify a
>pragma (I think) that allows autonomous commits. I will try writing a stored
>procedure sometime when time permits.
>
>

[Quoted] and that would do what here? Missing your point?

[Quoted] Here is what an autonomous transaction is about:

http://asktom.oracle.com/~tkyte/autonomous/index.html
>
>
>"Thomas Kyte" <tkyte_at_oracle.com> wrote in message
>news:adnsdj015gt_at_drn.newsguy.com...
>> 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_na
>me);
>> >> >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.
>>
>> 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.
>>
>> 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.
>>
>> 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.
>>
>> 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
>> WHERE c.id = sub_ref_id ) client_name,
>> decode( (SELECT COUNT(*) FROM addresses WHERE entity_id =
>sub_ref_id),
>> 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
>> from ( SELECT client_ext_attrs.*,
>> (SELECT sc.phone_number
>> FROM subscriber_channels sc
>> WHERE sc.subscriber_id = client_ext_attrs.client_id )
>>
>tel_p_nr,
>> ( SELECT DISTINCT clients.reference_id
>> FROM clients
>> WHERE clients.id = client_ext_attrs.client_id )
>sub_ref_id,
>> ( SELECT c.first_name || ';' || c.last_name
>> FROM clients c
>> WHERE c.id = client_ext_attrs.client_id )
>subscriber_name
>> 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')
>> 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
>> /
>>
>> is equivalent (once you pick off the proper columns in the final select
>and
>> concatenate them together) to all of your procedural logic.
>>
>> Likewise, you can do the same with the second query.
>>
>> Then, just create two views -- view1 and view2.
>>
>> Your script simply becomes:
>>
>> 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/
>> 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
>>
>
>

--
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/  
Opinions are mine and do not necessarily reflect those of Oracle Corp