Code Example: Send mail from PLSQL using Oracle UTL_SMTP Package.

Example of sending an email from PL/SQL using Oracle UTL_SMTP Package.

CREATE OR REPLACE PROCEDURE SEND_MAIL (
pSender    VARCHAR2,
pRecipient VARCHAR2,UTL_SMTP
pSubject   VARCHAR2,
pMessage   VARCHAR2) IS

mailhost  CONSTANT VARCHAR2(30) := '66.777.888.99';
crlf      CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
mesg      VARCHAR2(15000);
mail_conn UTL_SMTP.connection;

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 || pMessage;

   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);
EXCEPTION
  WHEN UTL_SMTP.transient_error
    OR UTL_SMTP.permanent_error THEN
    BEGIN
      UTL_SMTP.quit(mail_conn);
    EXCEPTION
      WHEN UTL_SMTP.transient_error
        OR UTL_SMTP.permanent_error THEN
        NULL;
    END;
      RAISE_APPLICATION_ERROR(-20000, SQLERRM);

 END SEND_MAIL ;