The notification mailer sometimes stops working. It is important to recognize such situations and fix them.
Here are the SQL queries that show the notification mailer’s status:
-- Check the number of notification mailer processes. If the number is 0, the mailer is not running. select running_processes from FND_CONCURRENT_QUEUES where concurrent_queue_name = 'WFMLRSVC';
-- Check the notification mailer status (should be “RUNNING”) select component_status from fnd_svc_components where component_id = (select component_id from fnd_svc_components where component_name = 'Workflow Notification Mailer'); |
Of course, the best check is to try to send email using the notification mailer.
Here is the PL/SQL block that sends email using the notification mailer:
set serveroutput on declare v_out number(10); v_sts wf_notifications.mail_status%type := ''; v_waited integer := 0; v_max_wait_time integer := 3600; -- 60 min v_start_date date := NULL; begin select sysdate into v_start_date from dual; v_out := wf_notification.send('<ROLE>', 'CS_MSGS', 'FYI_MESSAGE', sysdate, NULL, NULL, NULL, 1);
wf_notification.setattrtext(v_out,'OBJECT_TYPE','Testing Notification Mailer'); wf_notification.setattrtext(v_out,'SENDER','Oracle Workflow'); update wf_notifications set subject = 'Testing Notification Mailer' where notification_id = v_out; commit;
-- Check mail status -------------------- select mail_status into v_sts from wf_notifications where notification_id = v_out; v_waited := 0;
while v_sts != 'SENT' and v_waited <= v_max_wait_time loop dbms_lock.sleep(10); v_waited := v_waited + 10; select mail_status into v_sts from wf_notifications where notification_id = v_out; end loop; commit; dbms_output.put_line(v_sts); end; / |
If the notification mailer does not work it is necessary to start or restart it.
Here is the PL/SQL block that programmatically restarts the notification mailer:
set serveroutput on declare p_retcode number; p_errbuf varchar2(100); m_mailerid fnd_svc_components.component_id%TYPE; m_status fnd_svc_components.component_status%TYPE; m_max_iterations number := 30; m_iterations number := 0; begin
-- Find Notification Mailer component_id --------------------------------------------------- select component_id into m_mailerid from fnd_svc_components where component_name = 'Workflow Notification Mailer';
-- Check the Notification Mailer's status --------------------------------------------------- select component_status into m_status from fnd_svc_components where component_id = m_mailerid;
dbms_output.put_line('Current status: '||m_status);
if m_status not in ('DEACTIVATED_USER','DEACTIVATED_SYSTEM','STOPPED_ERROR') then -- Stop Notification Mailer --------------------------------- fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf);
if p_retcode != 0 then dbms_output.put_line('Could not stop Notification Mailer: '||p_errbuf); end if; commit; end if;
-- Check the Notification Mailer's status --------------------------------------------- select component_status into m_status from fnd_svc_components where component_id = m_mailerid;
while m_status not in ('DEACTIVATED_USER', 'DEACTIVATED_SYSTEM', 'STOPPED_ERROR') and m_iterations < m_max_iterations loop dbms_lock.sleep(10);
select component_status into m_status from fnd_svc_components where component_id = m_mailerid;
m_iterations := m_iterations + 1; end loop;
if m_status in ('DEACTIVATED_USER','DEACTIVATED_SYSTEM','STOPPED_ERROR') then -- Start Notification Mailer ---------------------------- fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);
if p_retcode != 0 then dbms_output.put_line('Could not start Notification Mailer: '||p_errbuf); end if; else dbms_output.put_line('Could not start Notification Mailer. Please, check it''s status'); end if; commit; exception when others then dbms_output.put_line(SQLERRM); end; / |
Comments
Hi Michael,
Nice section of code to restart the WF mailer. Very useful indeed.
Stuart
Leave a comment