Alter APEX_MAIL package-CodePudding

My Oracle Database cannot currently handle TLS 1.2 needed to communicate with Office 365 for mail. So I am using an old mail server to send out my mail because it does not need authentication and is located on premise. For internal communication I need to alter all "to" addresses to change the domain so the mail is sent to Microsoft and not back to the internal mail server.

Example: I need to change all emails from [email protected] to [email protected]. Is there a way I can alter the APEX_MAIL package in Oracle so that any use of domain.com in the "TO" field will automatically change to domain.onmicrosoft.com?

CodePudding user response：

Typically you don't change a proprietary package - even if you could, it would be wrong. Instead you wrap your own pl/sql procedure around apex_mail. Create a package with the same signature as the apex_mail procedures you are using and within those procedures invoke apex_mail. Before the call to apex_mail, do whatever magic you need (change the email adresses in your case). The only downside is that you cannot use this in processes of type "send email" - you'll have to use pl/sql. Here is an example of a procedure:

  PROCEDURE send (
      mail_to_i         IN VARCHAR2,
      mail_from_i       IN VARCHAR2,
      mail_reply_to_i   IN VARCHAR2,
      mail_body_i       IN VARCHAR2,
      mail_body_html_i  IN VARCHAR2 DEFAULT NULL,
      mail_subject_i    IN VARCHAR2 DEFAULT NULL,
      mail_cc_i         IN VARCHAR2 DEFAULT NULL,
      mail_bcc_i        IN VARCHAR2 DEFAULT NULL
      )
  IS

    l_mail_to             VARCHAR2(4000)  := NULL;
    l_mail_cc             VARCHAR2(4000)  := NULL;
    l_mail_bcc            VARCHAR2(4000)  := NULL;
  BEGIN
    l_mail_to := REPLACE (LOWER(mail_to_i),'@domain.com','@domain.onmicrosoft.com');
     -- do other substitutions if needed
      apex_mail.send
      (
        p_to        => l_mail_to,
        p_from      => mail_from_i,
        p_cc        => mail_cc_i,
        p_subj      => mail_subject_i,
        p_body      => mail_body_i,
        p_body_html => mail_body_html_i,
        p_replyto   => mail_reply_to_i,
        p_bcc       => mail_bcc_i
      );
  END send;

CodePudding user response：

You cannot and should not modify the APEX_MAIL package. You should modify the process that is calling the APEX_MAIL package to do any replacements if necessary or just validate the input of the email addresses so they are what you expect.

If you have emails that have already been added to apex_mail_queue and have errored, you can update apex_190200.wwv_flow_mail_queue (or whatever the proper APEX schema is for your environment) to correct the addresses in the MAIL_TO column. You will also need to reset mail_send_count and mail_send_error so that it is processed next time emails are attempted to be sent.

UPDATE apex_190200.wwv_flow_mail_queue
   SET mail_to = '[email protected]', mail_send_count = 0, mail_send_error = NULL;