Re: PL/SQL DBMS_OUTPUT buffer limit
Date: 6 Jun 2002 02:51:59 -0700
Message-ID: <815bc2a.0206060151.56c5da2_at_posting.google.com>
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.
[Quoted] The script, as explained in the first message requieres user input.
[Quoted] This input is a test-condition, also different data are retrieved from
[Quoted] 6-7 tables and a cursor is controlling it.
[Quoted] Also there are duplicates in database so additional testing is
[Quoted] requiered inside the loop to retrieve the latest and correct
instances.
[Quoted] [Quoted] On top of this the user input decides the format of the output file,
which can be one out of two.
[Quoted] So a simple select is wanted but not possible I'm afraid.
[Quoted] However, the problem has been solved using UTL_FILE package. Off
[Quoted] 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',
[Quoted] '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',
[Quoted] '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;
/ Received on Thu Jun 06 2002 - 11:51:59 CEST