APEX Logs: Storing Mail Log Data

A while ago (almost a year ago) I wrote about how to permanently store APEX logs into your own tables (http://apex-smb.blogspot.com/2009/05/apex-logs-storing-log-data.html). I covered the APEX_WORKSPACE_ACCESS_LOG and APEX_WORKSPACE_ACTIVITY_LOG logs. I forgot to include how to permanently store the APEX_MAIL_LOG. You'll only need to do this if you use APEX_MAIL to send email.

1- Create the APEX Mail Log table

-- Mail Log
CREATE TABLE tapex_mail_log
AS  SELECT * FROM apex_mail_log;

2- Update the APEX Mail Log table

Note: You may want to store this in a procedure and run as a nightly scheduled job so you don't forget to update the tables

BEGIN
  -- apex_mail_log is linked to the workspace.
  -- Need to access all the mail sent for each workspace that is linked to this schema

  FOR x IN (SELECT workspace_id FROM apex_workspaces) LOOP
    -- Set the workspace ID
    wwv_flow_api.set_security_group_id (x.workspace_id);

    INSERT INTO tapex_mail_log (mail_to,
                                mail_from,
                                mail_replyto,
                                mail_subj,
                                mail_cc,
                                mail_bcc,
                                mail_send_error,
                                last_updated_on)
      SELECT aml.mail_to,
             aml.mail_from,
             aml.mail_replyto,
             aml.mail_subj,
             aml.mail_cc,
             aml.mail_bcc,
             aml.mail_send_error,
             aml.last_updated_on
        FROM tapex_mail_log x, apex_mail_log aml
       WHERE     NVL (aml.mail_to, -1) = NVL (x.mail_to(+), -1)
             AND NVL (aml.mail_from, -1) = NVL (x.mail_from(+), -1)
             AND NVL (aml.mail_replyto, -1) = NVL (x.mail_replyto(+), -1)
             AND NVL (aml.mail_subj, -1) = NVL (x.mail_subj(+), -1)
             AND NVL (aml.mail_cc, -1) = NVL (x.mail_cc(+), -1)
             AND NVL (aml.mail_bcc, -1) = NVL (x.mail_bcc(+), -1)
             AND NVL (aml.mail_send_error, -1) = NVL (x.mail_send_error(+), -1)
             AND aml.last_updated_on = x.last_updated_on(+)
             AND x.ROWID IS NULL;
  END LOOP;
END;