Tuesday, April 13, 2010

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;

6 comments:

  1. Hi!

    Martin, can you tell me the reason of using an outer join, please? I think, a simple filter on apex_mail_log.last_updated_on in SELECT would be enough.

    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 apex_mail_log
    where last_updated_on >= sysdate;

    ReplyDelete
  2. Hi Alex,

    Thank you for your feedback. I'm using the outer join to perform an "anti-join". The main goal is to insert new rows into tapex_mail_log and avoid duplications. A colleague pointed out that I could have also done this using a MERGE statement (code included below).

    As per your suggestion we could probably could do a comparison just on the apex_mail_log.last_updated_on however the predicate would have to change a bit. It would be something like this:

    ...
    WHERE aml.last_updated_on > (SELECT NVL (MAX (x.last_updated_on), TO_DATE ('1900', 'YYYY')) FROM tapex_mail_log x);


    -- Merge statement mentioned above
    MERGE INTO tapex_mail_log t
    USING (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 apex_mail_log aml) aml
    ON ( NVL (aml.mail_to, -1) = NVL (t.mail_to, -1)
    AND NVL (aml.mail_from, -1) = NVL (t.mail_from, -1)
    AND NVL (aml.mail_replyto, -1) = NVL (t.mail_replyto, -1)
    AND NVL (aml.mail_subj, -1) = NVL (t.mail_subj, -1)
    AND NVL (aml.mail_cc, -1) = NVL (t.mail_cc, -1)
    AND NVL (aml.mail_bcc, -1) = NVL (t.mail_bcc, -1)
    AND NVL (aml.mail_send_error, -1) = NVL (t.mail_send_error, -1)
    AND NVL (aml.last_updated_on, SYSDATE) = NVL (t.last_updated_on, SYSDATE))
    WHEN NOT MATCHED THEN
    INSERT (t.mail_to,
    t.mail_from,
    t.mail_replyto,
    t.mail_subj,
    t.mail_cc,
    t.mail_bcc,
    t.mail_send_error,
    t.last_updated_on)
    VALUES (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);

    ReplyDelete
  3. Sorry, it seems like my previous post was lost. If it doubles, please delete this one.

    Thanks for your correction. With this one, the index on tapex_mail_log.last_updated_on will give a better execution plan in the future - based on RANGE SCAN access.

    Are you sure that your INSERT statement use the HASH ANTI-JOIN algorithm? I tried it with my example tables:

    CREATE TABLE TAPEX_MAIL_LOG_1
    (
    MAIL_TO VARCHAR2(2000 BYTE)
    , MAIL_FROM VARCHAR2(2000 BYTE)
    , MAIL_REPLYTO VARCHAR2(2000 BYTE)
    , MAIL_SUBJ VARCHAR2(2000 BYTE)
    , MAIL_CC VARCHAR2(2000 BYTE)
    , MAIL_BCC VARCHAR2(2000 BYTE)
    , MAIL_SEND_ERROR VARCHAR2(4000 BYTE)
    , LAST_UPDATED_ON DATE NOT NULL
    );
    CREATE INDEX TAPEX_MAIL_LOG_1_IDX1 ON TAPEX_MAIL_LOG_1 (LAST_UPDATED_ON);

    CREATE TABLE TAPEX_MAIL_LOG
    (
    MAIL_TO VARCHAR2(2000 BYTE)
    , MAIL_FROM VARCHAR2(2000 BYTE)
    , MAIL_REPLYTO VARCHAR2(2000 BYTE)
    , MAIL_SUBJ VARCHAR2(2000 BYTE)
    , MAIL_CC VARCHAR2(2000 BYTE)
    , MAIL_BCC VARCHAR2(2000 BYTE)
    , MAIL_SEND_ERROR VARCHAR2(4000 BYTE)
    , LAST_UPDATED_ON DATE NOT NULL
    );
    CREATE INDEX TAPEX_MAIL_LOG_IDX1 ON TAPEX_MAIL_LOG (LAST_UPDATED_ON);

    ----------------------------------
    SQL> select (select count(*)
    2 from (select last_updated_on from tapex_mail_log
    3 intersect
    4 select last_updated_on from tapex_mail_log_1)) intersect_cnt
    5 , (select count(*) from tapex_mail_log_1) log_1_cnt
    6 , (select count(*) from tapex_mail_log) log_cnt
    7 from dual
    8 ;

    INTERSECT_CNT LOG_1_CNT LOG_CNT
    ------------- ---------- ----------
    5000 10000 10000

    -----------------------------------
    All I can get was NL or HASH JOIN OUTER in the execution plan. To see HASH AJ, I had to change query as following:

    SELECT ...
    FROM tapex_mail_log_1 aml
    WHERE aml.last_updated_on > ( SELECT max(x.last_updated_on) FROM tapex_mail_log x );

    So, please check your INSERT statement on your environment. I think, you will not see HASH AJ too.

    Alex.

    PS: MERGE is great, but not in this case. More time to write more code, that does the same job.

    ReplyDelete
  4. > SELECT ...
    FROM tapex_mail_log_1 aml
    WHERE aml.last_updated_on > ( SELECT max(x.last_updated_on) FROM tapex_mail_log x );

    Sorry again, miss copying. That is what I mean:

    SELECT ...
    FROM tapex_mail_log_1 aml
    WHERE aml.last_updated_on not in ( select x.last_updated_on from tapex_mail_log x );

    ReplyDelete
  5. Hi Martin,
    This seems a long abandoned thread, and am just hoping to get a response.
    I have an apps developed using oracle APEX, and in it I have to send notification email to customers in respect of their payments, it is in form of a portal and therefore I use my host SMTP server to send the mail.
    The recommended email apps software is roundcube, now the application actually sends the email to the customers, but i need to be able to have evidence of email sent on the sent items list of the email address i am using to send the mail, viewing the mail box from roundcube. The APEX email log actually stores it, but I am at a loss to get this list on the addressee email box sent list.
    Thanks a lot.

    Daniel

    ReplyDelete
    Replies
    1. Hi Daniel,

      Please post your question on the APEX forums: forum.oracleapex.com

      Delete