PL/SQL DBMS_OUTPUT buffer limit
Kjell Ove Skarsbø
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?
Daniel Morgan
Try this at the beginning of your procedure
BEGIN
DBMS_OUTPUT.DISABLE;
DBMS_OUTPUT.ENABLE(1000000);
If that isn't large enough ... make the buffer larger.
Daniel Morgan
Tim Cross
> Kjell Ove Skarsbř wrote:
>
> Try this at the beginning of your procedure
>
> BEGIN
> DBMS_OUTPUT.DISABLE;
> DBMS_OUTPUT.ENABLE(1000000);
>
> If that isn't large enough ... make the buffer larger.
>
Can I ask for some clarification here? Still pretty much a newbie with
Oracle, but from all I have read, I thought there was a limit of
1000000 to the buffer size for DBMS_OUTPUT? If I've needed to output
more than 1Mb I've used temporary tables and then followed the plsql
block with a simple select on the temporary table. If you can actually
avoid this by setting a buffer larger than 1Mb life would be a lot
easier. If it can be set above this limit, what is the new limit and
on which Oracle/plsql versions can this be done?
Tim
Daniel Morgan
You are likely correct. I didn't count the zeros in my example.
Daniel Morgan
Rauf Sarwar
Up until Oracle 8i, the buffer limit has been 1MB. I have not seen
anything in Oracle 9i to believe that it has been changed. Your best
bet is to use UTL_FILE package and write your output to file. This
way, you don't have to deal with any buffer limits.
Regards
Daniel Morgan
I agree. And in addition you get the added benefit of superior error handling.
Daniel Morgan
Thomas Kyte
says...
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
--
Thomas Kyte (tk...@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
Kjell Ove Skarsbø
In a perfect world maybe, but not here.
The script, as explained in the first message requieres user input.
This input is a test-condition, also different data are retrieved from
6-7 tables and a cursor is controlling it.
Also there are duplicates in database so additional testing is
requiered inside the loop to retrieve the latest and correct
instances.
On top of this the user input decides the format of the output file,
which can be one out of two.
So a simple select is wanted but not possible I'm afraid.
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
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;
--
Scott Mattes
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.
"Thomas Kyte" <tk...@oracle.com> wrote in message
news:adnsd...@drn.newsguy.com...
Thomas Kyte
>
>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.
>
>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?
Here is what an autonomous transaction is about:
http://asktom.oracle.com/~tkyte/autonomous/index.html
Scott Mattes
> In article <LCNL8.4769$nn1.1...@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.
Based on my experience with other programming tools, I find it harder to do
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
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
since I have no access to the server it is useless to me (and others I
imagine).
I have also tried pipes with unsat results (there seems to be timing issues
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
>> In article <LCNL8.4769$nn1.1...@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.
>
>Based on my experience with other programming tools, I find it harder to do
>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
>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
>since I have no access to the server it is useless to me (and others I
>imagine).
well, like i said -- there are many many source code level debuggers -- you
know, those things you run and debug code in??
Anyway -- perhaps another tool of interest to you would be
dbms_application_info. It lets you update your row in v$session, setting upto 3
columns with whatever in "real time". Additionally you can use
v$session_longops with that package as well (to show the progress of your long
running procedure). No need to commit to see the data -- it is "just there"
>
>I have also tried pipes with unsat results (there seems to be timing issues
>I haven't gotten straight, maybe).
you probably had C on the other end and C uses BUFFERED IO. So, when you write,
it doesn't appear right away on screen (hey, hmm, sounds familar). Anyway, a
fflush() call in C would probably fix that up.
Scott Mattes
Thank you. V$session_longops might be an option, but v$session is not (at
this site at least).
"Thomas Kyte" <tk...@oracle.com> wrote in message
news:adtue...@drn.newsguy.com...