Post

How to programmatically restart the notification mailer

The notification mailer sometimes stops working. It is important to recognize such situations and fix them.

Workflow Mailer

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.

Solution:

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;

/

Contacts

www.RadugaApps.com

Feel free to contact our representative at [email protected]

Comments

Stuart Moore
30.09.2016

Hi Michael,
Nice section of code to restart the WF mailer. Very useful indeed.
Stuart

Leave a comment