I left out some important details
Patrick Mealey, January 26, 2003 - 12:29 am UTC
My DBA had told me about the ACTIVE session/shutdown immediate thing which got me off track. Your example got me back on track. The details I left out are significant to my problem. Consider this fragment:
set serveroutput on size 1000000
declare
alert_name VARCHAR2(30);
alert_msg VARCHAR2(1024);
alert_status NUMBER;
timeout NUMBER;
begin
alert_name := 'foo';
timeout := 30;
dbms_alert.register(alert_name);
loop
begin
--do various user defined actions here
dbms_alert.waitany(alert_name, alert_msg, alert_status, timeout);
if alert_status != 1 then
dbms_output.put_line('ending normally');
exit;
end if;
dbms_output.put_line('looping');
exception
when others then
--log the exception and keep going
dbms_output.put_line('ignoring exception');
end;
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
The code ignores exceptions in the loop because it is performing various user defined SQL tasks which may throw exceptions. These exceptions cannot be fatal to the loop because this is supposed to be a daemon-like process. The unfortunate side effect is that it ignores the exception caught during database shutdown which then causes a shutdown hang.
I will move the exception handler above the DBMS_ALERT.WAIT line to let any excpetions thrown during the WAIT get handled.
January 26, 2003 - 9:15 am UTC
Your code should be:
set serveroutput on size 1000000
declare
alert_name VARCHAR2(30) := 'foo';
alert_msg VARCHAR2(1024);
alert_status NUMBER;
timeout NUMBER := 30;
begin
dbms_alert.register(alert_name);
loop
begin
BEGIN
--do various user defined actions here
exception when others then
--log the exception and keep going
dbms_output.put_line('ignoring exception');
END;
dbms_alert.waitany(alert_name, alert_msg, alert_status, timeout);
exit when ( alert_status != 1 );
end loop;
end;
Your code goes into an infinite loop if the user defined action flings an exception time after time even in the BEST of situations. you had:
loop
user code
dbms_alert code
end loop
if user code flung an exception, you would goto the bottom of the code and do user code all over again. having dbms_output in there is not really useful as no one will ever see it.
A reader, January 28, 2004 - 8:47 am UTC