473,324 Members | 2,313 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Execution details in email notification

I am using the following function to send execution details of the process-flow. This process-flow is having 20 mappings. This is working fine.


The cIient requirment is to pass email-IDs from a back-end table(sam_mail_notification) with columns(smtp, port, from_address and to_address), right now the function is working on static(constant) values, Can I get the modified function from you.

--initialize variables here

-- main body
retval number := 0; --default

mailhost CONSTANT VARCHAR2(30) := 'EMAILSERVER';

crlf CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);

pSender VARCHAR2(30) := 'abc@dub.ae';

pRecipient VARCHAR2(30) := 'abc@dub.ae';

pSubject VARCHAR2(100) := 'Process REsults for: '||to_char(sysdate,'dd/mm/yyyy');

mesg VARCHAR2(32767);

mail_conn utl_smtp.connection;

cursor getResults is

select AREA.OBJECT_NAME,
AREA.CREATED_ON,
AREA.UPDATED_ON,
aramr.ELAPSE_TIME,
NUMBER_RECORDS_SELECTED,
NUMBER_RECORDS_INSERTED,
NUMBER_RECORDS_UPDATED,
NUMBER_ERRORS,
AREA.RETURN_RESULT,
AREA.EXECUTION_AUDIT_STATUS,
MESSAGE_SEVERITY,
MESSAGE_TEXT
from
all_Rt_audit_executions area,
all_Rt_audit_map_runs aramr,
all_rt_audit_exec_messages err
where AREA.execution_audit_id = ARAMR.execution_audit_id(+) AND
AREA.execution_audit_id = err.execution_audit_id(+)
and
trunc(area.created_on) = trunc(sysdate)
AND AREA.OBJECT_NAME IS NOT NULL AND AREA.TASK_TYPE!='ProcessFlow'
AND area.top_level_execution_audit_id =(select max(top_level_execution_audit_id)from all_Rt_audit_executions
where execution_name = (select execution_name from all_Rt_audit_executions where task_type ='ProcessFlow' and top_level_execution_audit_id =
(select max(top_level_execution_audit_id) from all_Rt_audit_executions)));

BEGIN

mail_conn := utl_smtp.open_connection(mailhost, 25);

mesg := 'Date: ' ||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf ||

'From: <'|| pSender ||'>' || crlf ||

'Subject: '|| pSubject || crlf ||

'To: '||pRecipient || crlf || '' || crlf ||

'MAPNAME START_TIME END_TIME ELAPSED SELECTED INSERTED UPDATED ERRORS RESULT AUDIT_STSTUS SEVERITY MESSAGE_TEXT'||crlf ;

for rec in getResults

loop

mesg:=mesg || rpad(rec.OBJECT_NAME,30)||

rpad(to_char(rec.CREATED_ON, 'HH24:MI:SS DD-MON-YY'),20)||

rpad(to_char(rec.UPDATED_ON, 'HH24:MI:SS DD-MON-YY'),24)||

rpad(to_char(rec.ELAPSE_TIME),7)||

rpad(to_char(rec.NUMBER_RECORDS_SELECTED),8)||

rpad(to_char(rec.NUMBER_RECORDS_INSERTED),10)||

rpad(to_char(rec.NUMBER_RECORDS_UPDATED),10)||

rpad(to_char(rec.NUMBER_ERRORS),5)||

rpad(to_char(rec.RETURN_RESULT),8)||

rpad(to_char(rec.EXECUTION_AUDIT_STATUS),10)||

rpad(to_char(rec.MESSAGE_SEVERITY),10)||

rpad(to_char(rec.MESSAGE_TEXT),200)||crlf;

end loop;

utl_smtp.helo(mail_conn, mailhost);

utl_smtp.mail(mail_conn, pSender);

utl_smtp.rcpt(mail_conn, pRecipient);

utl_smtp.data(mail_conn, mesg);

utl_smtp.quit(mail_conn);

return retval;

EXCEPTION

WHEN OTHERS THEN

return 1;
END;
Dec 23 '07 #1
0 1358

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Oliver | last post by:
I've been looking for information regarding this and all i can gather is posters informing posties to use $< and $>. However, i can't seem to find an example on how to apply that. Information...
27
by: vishnu mahendra | last post by:
cah you please tell me how to find the execution time of a program in c. thank you in advance, vishnu
2
by: Ravi J | last post by:
I am trying to load Microsoft Word and create a document in ASP.NET (C#). But the call to application creation 'Word._Application app = new Word.ApplicationClass();' takes quit a bit of time, and...
2
by: Bassel Tabbara | last post by:
I wrote the following code: oApp = new Outlook.Application(); oApp = new Outlook.Application(); oNameSpace= oApp.GetNamespace("MAPI"); oNameSpace.Logon(null,null,true,true); //gets defaultfolder...
8
by: NickName | last post by:
Hi, Say, you have two inter-dependent packages of pA and pB. With the successful execution of pA you would proceed to execute pB. And since they would perform some repeatitive tasks on regular...
23
by: roman | last post by:
Hi, I would like to have two actions for one event. But I want the second action to trigger when the first one action completes. Is it possible to do this in javascript? I'm using the onclick...
2
by: Kosmos | last post by:
Alright so I've got this Outlook code written in VBA in Access. The first part, which works, records information about appointment times based on the required days before notification of certain...
3
by: steven.rojas | last post by:
I have a chat application based on PHP and Flash, I know that is necessary execute the socket server script from the console (#!/usr/ local/bin/php -q), but my problem is that I don't have SSH...
2
by: Ethender | last post by:
Hi All, I am using the following function to send execution details of the process-flow. This process-flow is having 20 mappings. This is working fine. The cIient requirment is to pass...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.