How to put select statement into trigger to send email via UTL_SMTP?

CREATE OR REPLACE TRIGGER etl_mon.t_lotmsg_ih_tr
   BEFORE INSERT
   ON etl_mon.t_etl_err_lotmsg
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
DECLARE
   l_mailhost    VARCHAR2(64)       := 'mailserver.silterra.com';
                                         -- OR  server iP '255.255.255.255' ;
   l_from        VARCHAR2(64)       := 'From@silterra.com';
   l_subject     VARCHAR2(64)       := 'Alert: Lot Message 2 hours checking!';
   l_to          VARCHAR2(128)      := 'to@silterra.com';
   l_mail_conn   UTL_TCP.connection;
   rc            INTEGER;
BEGIN
   l_mail_conn := UTL_TCP.open_connection(l_mailhost, 25);
                                   -- open the SMTP port 25 on remote machine
   rc := UTL_TCP.write_line(l_mail_conn, 'HELO ' || l_mailhost);
   rc := UTL_TCP.write_line(l_mail_conn, 'MAIL FROM: ' || l_from || CHR(13));
   rc := UTL_TCP.write_line(l_mail_conn, 'RCPT TO: ' || l_to);
   rc := UTL_TCP.write_line(l_mail_conn, 'DATA');       -- Start message body
   rc := UTL_TCP.write_line(l_mail_conn, 'Subject: ' || l_subject || CHR(13));
   rc := UTL_TCP.write_line(l_mail_conn, '');
   rc := UTL_TCP.write_line(l_mail_conn, msg_text);
   rc := UTL_TCP.write_line(l_mail_conn, '.');
   rc := UTL_TCP.write_line(l_mail_conn, 'Please update AN on this.' || CHR(13));
   rc := UTL_TCP.write_line(l_mail_conn, '' || CHR(13));
   rc := UTL_TCP.write_line(l_mail_conn,'xxxx> Alert: Lot Message 2 hours checking! <xxxx'|| CHR(13)                       );
   rc := UTL_TCP.write_line(l_mail_conn, '' || CHR(13));
   rc := UTL_TCP.write_line(l_mail_conn, '' || CHR(13));
   rc := tl_tcp.write_line(l_mail_conn, 'Send by ETL_MON tringger' || CHR(13));
   rc := UTL_TCP.write_line(l_mail_conn, '' || CHR(13));
   rc := UTL_TCP.write_line(l_mail_conn, 'QUIT');
   UTL_TCP.close_connection(l_mail_conn);
END;

Hi All,

Sorry for confusing everyone.

The trigger is working to send mail to alert user when the cronjob run and go result to enter the mention table and it will trigger to send an email to user. But the email is just static email.

I try to the select statement to email but facing error.

select * from ETL_ERR_EXT_LOTS_DIM
     where RESULT_RETURN > (select ETL_LEVEL from MON_LEVEL where ETL_TYPE = 'EXT_LOTS_DIM')
     and VERIFY_TIME = (select max(VERIFY_TIME) from ETL_ERR_EXT_LOTS_DIM)
     order by VERIFY_TIME desc;

Hope you can teach me or let me know whather I can do it or not. I try to read from the web but can't really understand.

Hi All,

Just a simplify my question.

How to email to my user from Oracle when below event is performed and got output like below. In the email must include the output details.

select * from ETL_ERR_EXT_LOTS_DIM
     where RESULT_RETURN > (select ETL_LEVEL from MON_LEVEL where ETL_TYPE = 'EXT_LOTS_DIM')
     and VERIFY_TIME = (select max(VERIFY_TIME) from ETL_ERR_EXT_LOTS_DIM)
     order by VERIFY_TIME desc;

Output:
--------
VERIFY_TIME        LAST_INPUT_TIME    RESULT_RETURN
------------------ ------------------ -------------
17-Aug-09 14:00:00 17-Aug-09 13:41:28 881  

may be somethign like as shown below....

assuming your select statement will return only one record.. try the attached code...

CREATE OR REPLACE TRIGGER ETL_MON.T_LOTMSG_IH_TR
BEFORE INSERT
ON ETL_MON.T_ETL_ERR_LOTMSG 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
   l_mailhost    VARCHAR2 (64)       := 'mailserver.silterra.com';
   l_from        VARCHAR2 (64)       := 'From@silterra.com';
   l_subject     VARCHAR2 (64)      := 'Alert: Lot Message 2 hours checking!';
   l_to          VARCHAR2 (128)       := 'to@silterra.com';
   l_mail_conn   UTL_SMTP.connection;
 
   temp_var varchar2(1500):=null;
 
BEGIN
 
select VERIFY_TIME || LAST_INPUT_TIME   || RESULT_RETURN
into temp_var
from ETL_ERR_EXT_LOTS_DIM where RESULT_RETURN > (select ETL_LEVEL from MON_LEVEL where ETL_TYPE = 'EXT_LOTS_DIM') and VERIFY_TIME = (select max(VERIFY_TIME) from ETL_ERR_EXT_LOTS_DIM) order by VERIFY_TIME desc;
 
  l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
  UTL_SMTP.helo(l_mail_conn, l_mailhost);
  UTL_SMTP.mail(l_mail_conn, l_from);
  UTL_SMTP.rcpt(l_mail_conn, l_to);
 
  UTL_SMTP.open_data(l_mail_conn);
 
  UTL_SMTP.write_data(l_mail_conn, 'From: ' || l_from || Chr(13));
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || l_subject || Chr(13));
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || l_to || Chr(13));
  UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
 
 
    UTL_SMTP.write_data(l_mail_conn, 'Please update AN on this.' || Chr(13));
	UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
	UTL_SMTP.write_data(l_mail_conn, 'xxxx> Alert: Lot Message 2 hours checking! <xxxx' || Chr(13));
	UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
	UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
 
-- i have added here so that it gets concatenated into the email text..you can 
-- format it to whatever line you want and where it has to appear. also you can
-- get the column headings into the email text if you want by adding
-- one more static header line just before this line...
	UTL_SMTP.write_data(l_mail_conn, temp_var || Chr(13)); 
 
	UTL_SMTP.write_data(l_mail_conn, 'Send by ETL_MON tringger'|| Chr(13));
	UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
 
 
  UTL_SMTP.close_data(l_mail_conn);
 
  UTL_SMTP.quit(l_mail_conn);
END;
/

                                              

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:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:

Hi nav_kum_v,

Let me try now.

Hi nav_kum_v,

my cron job have below error:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
INTO T_ETL_ERR_ATTENDANCE
    *
ERROR at line 3:
ORA-04091: table ETL_MON.T_ETL_ERR_ATTENDANCE is mutating, trigger/function may
not see it
ORA-06512: at "ETL_MON.T_ATTENDANCE_MH_TR", line 12
ORA-04088: error during execution of trigger 'ETL_MON.T_ATTENDANCE_MH_TR'

Hi nav_kum_v,

Maybe can you shown me, how you will writte this below event in Oracle

1. Monitoring every 30 minutes and keep ouput in a table: T_ETL_ERR_ATTENDANCE

INSERT
INTO T_ETL_ERR_ATTENDANCE
select sysdate Verify_Time,
           Max(msg_state_WHEN) Last_Input,
           TO_CHAR((sysdate - Max(msg_state_WHEN)) * 1440, 99999) Result_Return
           from mybus.myom_mybus om, mybus.myoq_mybus oq
where om.mid = oq.mid
and msg_name like 'attendance';

2. Sent email to user

3. Inside the email contain the ouput .

Hi All experts,

Please help also.

Thanks.

Below are the email currently, I can produce:

From: xxxx
Sent: None
To: xxxx

Subject: Alert: ext_lots_dim data lagging!
Please update Aun Nee on this.
xxxx> Alert: ext_lots_dim data lagging! <xxxx
 
Send by ETL_MON tringger
 
Bcc:
Return-Path: DIST-IT-ETLMON@silterra.com
Message-ID: <MYITEX01FntS72ebejm0004d254@MYITEX.silterra.com>
X-OriginalArrivalTime: 27 Aug 2009 06:00:04.0595 (UTC) FILETIME=[9F4F5430:01CA26DB]
Date: 27 Aug 2009 14:00:04 +0800

can u give me the exact/complete trigger code which gave the below error..

<<Hi nav_kum_v,
my cron job have below error:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
INTO T_ETL_ERR_ATTENDANCE
    *
ERROR at line 3:
ORA-04091: table ETL_MON.T_ETL_ERR_ATTENDANCE is mutating, trigger/function may
not see it
ORA-06512: at "ETL_MON.T_ATTENDANCE_MH_TR", line 12
ORA-04088: error during execution of trigger 'ETL_MON.T_ATTENDANCE_MH_TR'
>>

i am not sure what is the below code because the code what i gave you is trigger code which does not contain the below code...

Pls. let us deal one issue at a time here otherwise everyone will be confused.

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Hi nav_kum_v,

Maybe can you shown me, how you will writte this below event in Oracle

1. Monitoring every 30 minutes and keep ouput in a table: T_ETL_ERR_ATTENDANCE

INSERT
INTO T_ETL_ERR_ATTENDANCE
select sysdate Verify_Time,
           Max(msg_state_WHEN) Last_Input,
           TO_CHAR((sysdate - Max(msg_state_WHEN)) * 1440, 99999) Result_Return
           from mybus.myom_mybus om, mybus.myoq_mybus oq
where om.mid = oq.mid
and msg_name like 'attendance';


2. Sent email to user

3. Inside the email contain the ouput
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Hi nav_kum_v,

Item 1 is the event which fire the trigger to sent the email.

1. Cron job monitoring every 30 minutes and keep ouput in a table: T_ETL_ERR_ATTENDANCE

INSERT
INTO T_ETL_ERR_ATTENDANCE
select sysdate Verify_Time,
           Max(msg_state_WHEN) Last_Input,
           TO_CHAR((sysdate - Max(msg_state_WHEN)) * 1440, 99999) Result_Return
           from mybus.myom_mybus om, mybus.myoq_mybus oq
where om.mid = oq.mid
and msg_name like 'attendance';

2. Trigger to sent email to user:

2.1. Inside the email contain the ouput .

ok..if that is the case, then have the trigger to select from this table T_ETL_ERR_ATTENDANCE instead of the select which i had put in there in the trigger code and then give it a try.

but just ensure your select statement should retrieve only one record when you query T_ETL_ERR_ATTENDANCE

let me know if you need help with the code or if you get some error.

if you get any error, please paste both the complete code which gave the error and the error as well.

I have what might be a simpler way to do this.  Assuming you have a CRON JOB on a Unix platform that does some ETL, and as a final step you want it to send you, or some person or distribution list some info on the results.

And assuming that sendmail is working from that platform, then you may simply add a few lines to the end of the cron job, something like this in a bourne shell / korn shell or bash shell...

sqlplus $connectstr @check_etl > check_etl.out
(
echo "From: Barack.Obama@whitehouse.gov"
echo "Subject: ETL Results"
echo " "
cat check_etl.out
) | mail -tw silterra@yahoo.com

And of course you can put some name other than Barack, so in case the person who gets this email needs to reply to someone, you can put the person responsible for the ETL or answering question about what the message means there.

And of course if you want to imbed other logs, such as sql*loader log, it may also be helpful.  I generally try to do this with multiple layers where I have a simple/summary email that goes to larger distribution with short message, like just row counts loaded and success/failure, and then a more detailed log that goes to selected developer/power user types that have a need to look into the details of what failed.

Since the asker is already having the pl/sql code which is already sending the email but just he wanted to add some little more text which is nothing but the output of some sql statement and hence i think he can stick to the same method otherwise he may have to get the unix script working from scratch ( though he has cron entry )  ....

anyways, it is his choice on which method he wants..

Hi all,

Still can resolve the mutation error:

SQL*Plus: Release 9.2.0.5.0 - Production on Thu Sep 3 18:22:55 2009
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

1 row created.

1 row created.
INTO T_ETL_ERR_EXT_LOTS_DIM
    *
ERROR at line 3:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "ETL_MON.T_EXT_LOTS_DIM_IH_TR", line 12
ORA-04088: error during execution of trigger 'ETL_MON.T_EXT_LOTS_DIM_IH_TR'
 
1 row created.

Commit complete.
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
$

Please help

Hi All,

After rethink, can anyone of you teach me how to read a trigger to fire function (which can send email)?

if you get any error, please paste both the complete code which gave the error and the error as well. --> i already put that in my last update itself.

If we need help, then at least i need that info. to understand what code you are executing to get that error.

see the error there which you had got ---> "ORA-01422: exact fetch returns more than requested number of rows"  <<<<< for this also i had already put something in my last update itself >>> "but just ensure your select statement should retrieve only one record when you query T_ETL_ERR_ATTENDANCE "

attach the full code here and i can help... it should be simple, some select is returning more than 1 record to give that error.

1. Cronjob at HP-UX running this sql:

INSERT
INTO T_ETL_ERR_ATTENDANCE
select sysdate Verify_Time,
           Max(msg_state_WHEN) Last_Input,
           TO_CHAR((sysdate - Max(msg_state_WHEN)) * 1440, 99999) Result_Return
           from mybus.myom_mybus om, mybus.myoq_mybus oq
where om.mid = oq.mid
and msg_name like 'attendance';

2. Trigger at T_ETL_ERR_ATTENDANCE:

CREATE OR REPLACE TRIGGER ETL_MON.EALERT_ATTENDANCE_TR
AFTER INSERT
ON ETL_MON.T_ETL_ERR_ATTENDANCE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
l_mailhost VARCHAR2 (64) := 'xxxxx.s.com';
l_from VARCHAR2 (64) := 'xxxxx@s.com';
l_subject VARCHAR2 (64) := 'Alert: Attendance ETL down!';
l_to VARCHAR2 (128) := 'xxxxx@s.com';
l_mail_conn UTL_SMTP.connection;
BEGIN
l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
UTL_SMTP.helo(l_mail_conn, l_mailhost);
UTL_SMTP.mail(l_mail_conn, l_from);
UTL_SMTP.rcpt(l_mail_conn, l_to);
UTL_SMTP.open_data(l_mail_conn);
UTL_SMTP.write_data(l_mail_conn, 'From: ' || l_from || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || l_subject || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'To: ' || l_to || Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
 
UTL_SMTP.write_data(l_mail_conn, 'Please update Aun Nee on this.' || Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'xxxx> Alert: Attendance ETL down! <xxxx' || Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'Send by ETL_MON tringger'|| Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
 
UTL_SMTP.close_data(l_mail_conn);
UTL_SMTP.quit(l_mail_conn);
END;

 

3. Now want to put the inserted result in the email show the reader can understand what is going on. Instead of fix email.

ok..now tell me which one gave the below error ...just say 1) or 2 ) from the above.

1 row created.
INTO T_ETL_ERR_EXT_LOTS_DIM
    *
ERROR at line 3:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "ETL_MON.T_EXT_LOTS_DIM_IH_TR", line 12
ORA-04088: error during execution of trigger 'ETL_MON.T_EXT_LOTS_DIM_IH_TR'
 
1 row created.

CAN U PLS CLARIFY --> there is no commit in the 1) code in the crontab, so how come this trigger can see that inserted data....

Error happen at  1 after add in the select statement in item 2.

Below are the new code:

1. Cronjob at HP-UX running this sql:
INSERT
INTO T_ETL_ERR_ATTENDANCE
select sysdate Verify_Time,
           Max(msg_state_WHEN) Last_Input,
           TO_CHAR((sysdate - Max(msg_state_WHEN)) * 1440, 99999) Result_Return
           from mybus.myom_mybus om, mybus.myoq_mybus oq
where om.mid = oq.mid
and msg_name like 'attendance';

2. After update new change on trigger:

CREATE OR REPLACE TRIGGER ETL_MON.T_EXT_LOTS_DIM_IH_TR
AFTER INSERT
ON ETL_MON.T_ETL_ERR_EXT_LOTS_DIM
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_RETURN_RESULT VARCHAR2 (888) :=null;
l_mailhost VARCHAR2 (64) := 'xxxxx.silterra.com';
l_from VARCHAR2 (64) := xxxxx@silterra.com';
l_subject VARCHAR2 (64) := 'Alert: ext_lots_dim data 2 hours checking! ';
l_to VARCHAR2 (128) := xxxxxx@silterra.com';

l_mail_conn UTL_SMTP.connection;
BEGIN
select VERIFY_TIME || LAST_INPUT_TIME || RESULT_RETURN
into V_RETURN_RESULT
from T_ETL_ERR_EXT_LOTS_DIM
where VERIFY_TIME LIKE TO_DATE(sysdate, 'DD-MM-YYYY');

l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);

UTL_SMTP.helo(l_mail_conn, l_mailhost);
UTL_SMTP.mail(l_mail_conn, l_from);
UTL_SMTP.rcpt(l_mail_conn, l_to);
UTL_SMTP.open_data(l_mail_conn);
UTL_SMTP.write_data(l_mail_conn, 'From: ' || l_from || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || l_subject || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'To: ' || l_to || Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
 
UTL_SMTP.write_data(l_mail_conn, 'Please update Aun Nee on this.' || Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'xxxx> Alert: ext_lots_dim data 2 hours checking! <xxxx' || Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'Send by ETL_MON tringger'|| Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
UTL_SMTP.write_data(l_mail_conn, V_RETURN_RESULT || Chr(13));
UTL_SMTP.close_data(l_mail_conn);
UTL_SMTP.quit(l_mail_conn);
END;

Below error shown:

INTO T_ETL_ERR_EXT_LOTS_DIM
    *
ERROR at line 3:
ORA-04091: table ETL_MON.T_ETL_ERR_EXT_LOTS_DIM is mutating, trigger/function
may not see it
ORA-06512: at "ETL_MON.T_EXT_LOTS_DIM_IH_TR", line 12
ORA-04088: error during execution of trigger 'ETL_MON.T_EXT_LOTS_DIM_IH_TR'

be consistent in what you are saying. i am getting confused because just before this update you told you are getting the error

"INTO T_ETL_ERR_EXT_LOTS_DIM
    *
ERROR at line 3:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "ETL_MON.T_EXT_LOTS_DIM_IH_TR", line 12
ORA-04088: error during execution of trigger 'ETL_MON.T_EXT_LOTS_DIM_IH_TR' "

but now you are saying

"INTO T_ETL_ERR_EXT_LOTS_DIM
    *
ERROR at line 3:
ORA-04091: table ETL_MON.T_ETL_ERR_EXT_LOTS_DIM is mutating, trigger/function
may not see it
ORA-06512: at "ETL_MON.T_EXT_LOTS_DIM_IH_TR", line 12
ORA-04088: error during execution of trigger 'ETL_MON.T_EXT_LOTS_DIM_IH_TR'"

Not sure why everytime you change the update which is confusing to everybody who are trying to help you out ?

also please help to provide your comment to the question/info. requested from you which i had put in my prev. update.

CAN U PLS CLARIFY --> there is no commit in the 1) code in the crontab, so how come this trigger can see that inserted data.... ? you never commented for this. I am confused because one session is trying to insert into some table from crontab and it does not have commit.. how come the other session can read the data inserted into this table.. a very basic thing is missing here.. can you comment.

Hi nav_kum_vi,

You are right!

Missing commit.

Let me try.

also reply to this one...

T_ETL_ERR_ATTENDANCE --> you are inserting into this table from crontab ( assuming you have a commit as well there ), but i do not see a select statement to this table in your trigger code -> why is it so ?

as per your earlier updates, you told the data inserted by the crontab script will be used by the trigger code to select from that table and send in the form of an email.

Hi nav_kumar_v,

Myself also confusing.

I changing flow.

1. Cronjob remain (Create event)

2. Trigger call function (Create new trigger to call function only)

3. Function to email (select and send email)

Hope it work.

Hi,

Here is the select statement:

Select * from ETL_ERR_ATTENDANCE
where RESULT_RETURN > (select ETL_LEVEL from MON_LEVEL where ETL_TYPE = 'ATTENDANCE')
and VERIFY_TIME LIKE TO_DATE(sysdate, 'DD-MM-YYYY')
order by VERIFY_TIME desc;

Below are the example output:

VERIFY_TIME,LAST_INPUT_TIME,RESULT_RETURN
06-Sep-09 11:00:00,05-Sep-09 00:02:40,  2097
06-Sep-09 10:00:01,05-Sep-09 00:02:40,  2037
06-Sep-09 09:00:01,05-Sep-09 00:02:40,  1977
06-Sep-09 08:00:01,05-Sep-09 00:02:40,  1917
06-Sep-09 07:00:00,05-Sep-09 00:02:40,  1857
06-Sep-09 06:00:01,05-Sep-09 00:02:40,  1797
06-Sep-09 05:00:00,05-Sep-09 00:02:40,  1737
06-Sep-09 04:00:00,05-Sep-09 00:02:40,  1677
06-Sep-09 03:00:01,05-Sep-09 00:02:40,  1617
06-Sep-09 02:00:01,05-Sep-09 00:02:40,  1557
06-Sep-09 01:00:00,05-Sep-09 00:02:40,  1497
06-Sep-09,05-Sep-09 00:02:40,  1437

YES, YES, YES, tomcatkev.

This is what I wanted. Just one more help, how to include time for:

||:new.verify_time||' '||:new.last_input||'

Below are the result of email sent:

Subject: Alert: Attendance ETL down! 07-SEP-09 07-SEP-09 6
Date: 07-SEP-2009 09:30:00
Please update Aun Nee on this.
xxxx> ETL Monitoring Alert: Attendance ETL Lagging! <xxxx
07-SEP-09
07-SEP-09
6
Sent by: ETL_MON tringger

Finally work.

Below are the completed coding to share:

1. Cron job to create insert event:
INSERT
INTO T_ETL_ERR_ATTENDANCE
select sysdate Verify_Time,
Max(msg_state_WHEN) Last_Input,
TO_CHAR((sysdate - Max(msg_state_WHEN)) * 1440, 99999) Result_Return
from mybus.myom_mybus om, mybus.myoq_mybus oq
where om.mid = oq.mid
and msg_name like 'attendance';

2. trigger to send email:

CREATE OR REPLACE TRIGGER ETL_MON.TEST_EALERT_ATTENDANCE_TR
AFTER INSERT
ON ETL_MON.T_ETL_ERR_ATTENDANCE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
l_mailhost VARCHAR2 (64) := 'MYITEX.s.com';
l_from VARCHAR2 (64) := 'm@s.com';
l_to VARCHAR2 (128) := 'm@s.com';
l_subject VARCHAR2 (64) := 'Alert: Attendance ETL down! Now lagging: '||:new.Result_Return;
DF_Last_Input_Time VARCHAR2 (20);

l_mail_conn UTL_SMTP.connection;
BEGIN
DF_Last_Input_Time :=to_char(:new.LAST_INPUT_TIME,'DD-MON-YYYY HH24:MI:SS');

l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
UTL_SMTP.helo(l_mail_conn, l_mailhost);
UTL_SMTP.mail(l_mail_conn, l_from);
UTL_SMTP.rcpt(l_mail_conn, l_to);
UTL_SMTP.open_data(l_mail_conn);
UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'From: ' || l_from || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || l_subject || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'To: ' || l_to || Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'Please update ETL admin on this.' || Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'Verify Date & Time: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'Last Update Time: ' ||DF_Last_Input_Time|| Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'Lagging: ' ||:new.Result_Return|| Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'Sent by: ETL_MON tringger'|| Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));
 
UTL_SMTP.close_data(l_mail_conn);
UTL_SMTP.quit(l_mail_conn);
END;
 

3. Email output wanted:

Subject: Alert: Attendance ETL down! Now lagging: 7
Please update ETL admin on this.
 
Verify Date & Time: 07-SEP-2009 10:19:41
Last Update Time: 07-SEP-2009 10:13:07
Lagging: 7
 

Sent by: ETL_MON tringger