E-Mail from Oracle Procedures...

UTL_SMTP is the package that can be utilized to send email from Oracle Procedures. Its nothing new as its been there since 8i!!!
Here is the simple code and an example how to use it:


CREATE OR REPLACE PROCEDURE send_mail
(p_to        IN VARCHAR2,
 p_from      IN VARCHAR2,
 p_message   IN VARCHAR2,
 p_subject   in VARCHAR2,
 p_smtp_host IN VARCHAR2,
 p_smtp_port IN NUMBER DEFAULT 25)
IS
  t_mail_conn   UTL_SMTP.connection;
BEGIN

  t_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(t_mail_conn, p_smtp_host);
  UTL_SMTP.mail(t_mail_conn, p_from);
  UTL_SMTP.rcpt(t_mail_conn, p_to);

  UTL_SMTP.open_data(t_mail_conn);
 
  UTL_SMTP.write_data(t_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(t_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
  UTL_SMTP.write_data(t_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(t_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
  UTL_SMTP.write_data(t_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf || UTL_TCP.crlf);
 
  UTL_SMTP.write_data(t_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
  UTL_SMTP.close_data(t_mail_conn);

  UTL_SMTP.quit(t_mail_conn);
 
END san_send_mail;
/

Example of using the above procedure to send an email:


BEGIN
  send_mail(p_to    => 'to.me@me.com',
            p_from      =>  'to.me@me.com',
            p_message   => 'This is a test message from send_mail procedure... ',
            p_subject   => 'Test MSG - for Email Notifications...',
            p_smtp_host => 'Your.SMTP.SERVER.Address.'); --check SMTP_OUT_SERVER parameter for this value
END;
/