Re: PL/SQL DBMS_OUTPUT buffer limit

"Thomas Kyte" <tkyte_at_oracle.com> wrote in message news:adoo8f0179l_at_drn.newsguy.com...
> In article <LCNL8.4769$nn1.1631693_at_news1.news.adelphia.net>, "Scott
says...
> >
> >Oracle provides such limited debugging help!
>
> huh? what would that have to do with anything? In any case, there are
source
> code level debuggers (many) available for plsql. My preference is a
simple
> debug routine that works just like your forms one but used UTL_FILE on the
> server.

[Quoted] Based on my experience with other programming tools, I find it harder to do [Quoted] some basic things in Oracle. Imagine, a debug tool like DBMS_OUTPUT that

1. only shows its output AFTER the run is over (this is sure wonderful [Quoted] for loop debugging, when the loop never returns),
2. has a limit as to how much it can buffer (even correctly running loops that process many records will overflow the max buffer limit),
3. when the buffer limit is hit ALL output is GONE!

UTL_FILE would be great, since it works with forms and straight pl/sql, but [Quoted] since I have no access to the server it is useless to me (and others I imagine).

[Quoted] [Quoted] I have also tried pipes with unsat results (there seems to be timing issues [Quoted] I haven't gotten straight, maybe).

>
> >
> >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.
> >
> >
>
> and that would do what here? Missing your point?

By using Autonomous Transactions with the basics of my Forms proc I get instantly available view of the output in all the flavors of Oracle tools (it also amazes me that not all Oracle tools have the same capabilities when it comes to pl/sql). Just keep my small footprint sql query program running (not sqlplus) and hit the query button occasionally.

>
> Here is what an autonomous transaction is about:
>
> http://asktom.oracle.com/~tkyte/autonomous/index.html

Thank you for the link, now to play with it.

> >
> >
> >"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_addr
1
>
>||';'||c_addr2||';'||c_zip||';'||c_p_area||';'||s_first_name||';'||s_last_n
a
> >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
>
Received on Fri Jun 07 2002 - 16:01:24 CEST