Hello,
I would like to log most of my script's activities using redirected dbms_output to the logfile, however time of the time it crashes due to the ORA-20000: ORU-10027: buffer overflow, limit of xy bytes error. I would like it to crash "honestly" and to have in my logfile when it crashed and why it crashed. Let's have the following block:
declare
l_loops number;
too_verbose exception;
pragma exception_init (too_verbose, -20000);
begin
dbms_output.enable(buffer_size => 2500); --max is 1000000 (or NULL for infinite buffer, which is not desirable)
--dbms_output.enable(buffer_size => null);
dbms_output.put_line('Program started at ' || to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss'));
for i in 1 .. 100 -- then add 0, so the loop count is 1000
loop
dbms_output.put_line('processing loop ' || i);
l_loops := i;
end loop;
--intentionally raise an exception
raise_application_error(-20001,'Intentionally raised exception');
exception
when too_verbose then
--dbms_output.disable();dbms_output.enable(); => lost all that was written till now
dbms_output.enable(buffer_size => 3000);--increase the buffer size
dbms_output.put_line('Program failed at ' || to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss'));
dbms_output.put_line('Program died because of its own verbosity after ' || l_loops || ' loops.');
dbms_output.put_line('Error message: ' || sqlerrm);
when others then
dbms_output.put_line('Program failed at ' || to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss'));
dbms_output.put_line('Something went wrong: ' || sqlerrm);
end;
if I run it as is, the output is (exactly as expected)
Program started at 2017/11/22 13:35:11
processing loop 1
processing loop 2
..
..
processing loop 99
processing loop 100
Program failed at 2017/11/22 13:35:11
Something went wrong: ORA-20001: Intentionally raised exception
but if the number of loops is increased to 1000 to intentionally overflow the dbms_output, the output is
Program started at 2017/11/22 13:35:58
processing loop 1
processing loop 2
..
..
processing loop 134
processing loop 135
Program failed at 2017/11/22 14:42:49
Program died because of its own verbosity after 135 loops.
Error message: ORA-20000: ORU-10027: buffer overflow, limit of 2500 bytes
but the buffer had to be increased in the error handling part, which I kind of do not like. If there was not the command
dbms_output.enable(buffer_size => 3000);--increase the buffer size
the block would raise ORA-20000: ORU-10027: buffer overflow, limit of 2500 bytes as the dbms_output buffer is already full.
Another option is to enable the unlimited dbms_output.enable(NULL) but this seems to be a quite "dangerous" approach because after some testing what in fact "infinite" means my testing XE instance became almost unresponsive and remained like that for some time even after the routine crashed (I forgot the error but it's not that important). So I'm pretty sure I don't want to ever use dbms_output.enable(NULL) in production.
So I wonder how others deal with possibly verbose logging.
Also how is it possible that it produces ORA-20000? I thought the range -20000-20999 was reserved for application errors.
Thanks a lot,
Pavel