Oracle UTL_MAIL
This is an example of using PL/SQL to send plain text email from Oracle database using UTL_MAIL. For this to work, the database should be configured for SMTP (along with related Java classes/ JVM ). The UTL_MAIL package is designed for SMTP communication specified by IETF RFC-821. For the example below, the actual SMTP mail server name has to be used (l_mailhost). The code can be enhanced to send mail with file attachments. The other PL/SQL utility function is UTL_SMTP.

Oracle UTL_MAIL 
DECLARE
 PROCEDURE send_mail(p_sender IN VARCHAR2,
    p_recipient IN VARCHAR2,
    p_message IN VARCHAR2) AS
  l_mailhost   VARCHAR2(255) := 'smtp_server_name';  -- use local SMTP server
  l_mail_conn  utl_smtp.connection;
 BEGIN
   l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);
   utl_smtp.helo(l_mail_conn, l_mailhost);
   utl_smtp.mail(l_mail_conn, p_sender);
   utl_smtp.rcpt(l_mail_conn, p_recipient);
   utl_smtp.open_data(l_mail_conn );
   utl_smtp.write_data(l_mail_conn, p_message);
   utl_smtp.close_data(l_mail_conn );
   utl_smtp.quit(l_mail_conn);
 END send_mail;

BEGIN
  send_mail('sender@domain.com',  'recipient@domain.com',
    CHR(10)||'SMTP Test ....'||CHR(13)||
   'Sent From '||SUBSTR(UTL_INADDR.GET_HOST_NAME,1,40)||' On'||CHR(10)||TO_CHAR(sysdate,'mm/dd/yyyy hh24:mi:ss'));
END;
/

Oracle registered trademark of Oracle Corporation.
Last Revised On: October 13th, 2013

  55043