Tuesday, June 30, 2015

PROCEDURE to send email

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