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;