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

1 2 3 4 5

-- 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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

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;