Martin Giffy D'Souza on Oracle APEX: APEX Logs: Storing Mail Log Data

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:

suPPLerApril 14, 2010 at 3:52 AM
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
Replies
Martin D'SouzaApril 14, 2010 at 5:48 AM
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
Replies
suPPLerApril 14, 2010 at 10:06 AM
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
Replies
suPPLerApril 14, 2010 at 10:11 AM
> 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
Replies
AnonymousMay 13, 2014 at 10:55 AM
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


Oracle Application Express Plugins	Expert Oracle Application Express

Beginning Oracle Application Express 4	Expert Oracle Application Express 5

Pages

Tuesday, April 13, 2010

APEX Logs: Storing Mail Log Data

6 comments: