Saturday, February 13, 2016

Tip of the day: APEX mail & max emails per workspace

It was almost perfect winter day. Sunny with fresh snow. Perfect day for running up and down my favorite mountain.



I was enjoying every bit of nature and all of a sudden: Beeep, beeep! The sound of a SMS. Damn! I've ignored it at first. Two seconds later Beeep, beeep! Beeep, beeep! I thought, it must be something important...and yes it was. It was my boss, messaging me that yesterdays PL/SQL job that sends e-mail from our HR APEX app didn't send e-mail to around 500 clients from 1500 total.
Strange thing, but my Mac was miles away and I couldn't help at the moment (and yes, it was urgent) so I've decided to turn around and head home.

First thing that I've did when I came home was checking APEX mail log. No errors there, no mails in a queue - sounds good. I thought it must be something from the processing part that runs in a PL/SQL job. Then I've checked error log and there it was:

You have exceeded the maximum number of email messages per workspace.  Please contact your administrator. 

Omg, what's that?! It's APEX instance parameter that defines the number of email messages that can be sent with the APEX_MAIL API per workspace per 24 hour period. Omg! :)

To fix this you have to log on as APEX instance admin user and go to Manage Instance > Instance Settings > Email:


So, if you don't want to be bothered in those great moments, don't forget to change it. It can save the day.



Also, If you want to query apex_mail_log or apex_mail_queue dictionary views you have to set workspace (security_group_id) before:

begin
  apex_util.set_security_group_id(apex_util.find_security_group_id(:WS_NAME));
end;



Tested on APEX 5.0.3.00.03



2 comments:

  1. How do you resend the messages that did not send(after the max was exceeded)?

    ReplyDelete
    Replies
    1. If they are still in queue, you can do apex_mail.push_queue or log in as APEX instance admin and under the Manage Instance > Mail Queue you have buttons Force Send All Mail and Send All Mail.

      Br,
      Marko

      Delete