This Procedure have two input parameters for email body.
CREATE OR REPLACE PROCEDURE "SEND_EMAIL"(
p_message_body IN VARCHAR2,
p_recipient1 IN VARCHAR2 DEFAULT 'saman@test.com',
p_recipient2 IN VARCHAR2 DEFAULT 'kamal@test.com',
p_sender IN VARCHAR2 DEFAULT 'oracle_database@test.com',
p_subject IN VARCHAR2 DEFAULT 'PRODUCTION DATABASE EMAIL',
p_cc IN VARCHAR2 DEFAULT 'nimal@test.com',
p_mailport IN NUMBER DEFAULT 25,
p_mailhost IN VARCHAR2 DEFAULT 'localhost' )
IS
l_mail_conn UTL_SMTP.connection;
crlf CHAR(2) := CHR(13)||CHR(10);
l_message VARCHAR2(32767);
BEGIN
l_message := 'Date: '||TO_CHAR(sysdate,'dd Mon yy hh24:mi:ss')||crlf;
l_message := l_message||'From: '||p_sender||crlf;
l_message := l_message||'To: '||p_recipient1||';'||p_recipient2||';'||crlf;
l_message := l_message||'CC: '||p_cc||crlf;
l_message := l_message||'Subject: '||p_subject||crlf;
l_message := l_message||crlf||p_message_body;
l_mail_conn := utl_smtp.open_connection(p_mailhost, p_mailport);
utl_smtp.helo(l_mail_conn, p_mailhost);
utl_smtp.mail(l_mail_conn, p_sender);
utl_smtp.rcpt(l_mail_conn, p_recipient1);
utl_smtp.rcpt(l_mail_conn, p_recipient2);
utl_smtp.rcpt(l_mail_conn, p_recipient3);
utl_smtp.rcpt(l_mail_conn, p_recipient4);
utl_smtp.rcpt(l_mail_conn, p_cc);
utl_smtp.data(l_mail_conn, l_message);
utl_smtp.quit(l_mail_conn);
EXCEPTION
WHEN OTHERS THEN
raise_application_error( -20000,'Failed to send mail due to the following error: ' ||crlf|| sqlerrm);
END;
No comments:
Post a Comment