Re: PL/SQL DBMS_OUTPUT buffer limit
Date: Thu, 06 Jun 2002 18:12:59 GMT
Message-ID: <LCNL8.4769$nn1.1631693_at_news1.news.adelphia.net>
[Quoted] [Quoted] Oracle provides such limited debugging help!
[Quoted] [Quoted] 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 [Quoted] 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] [Quoted] "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
[Quoted] ||';'||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.
>> >requiered inside the loop to retrieve the latest and correct
> >Also there are duplicates in database so additional testing is
> >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;
> > > >> >END;
> >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');
> >/
>> Expert one on one Oracle, programming techniques and solutions for Oracle.
> --
> Thomas Kyte (tkyte_at_oracle.com) http://asktom.oracle.com/
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
> Received on Thu Jun 06 2002 - 20:12:59 CEST