Sending Email – PL/SQL

A quick post on how to send an email with PL/SQL.

There’s a LOT of documentation available at the following link:
http://www.orafaq.com/wiki/Send_mail_from_PL/SQL

  1. PROCEDURE EMAIL_ERROR_REPORT IS
  2.  
  3.        /* Create vars */
  4.        v_From        VARCHAR2 (80) := 'test@test.com';
  5.        v_Recipient   VARCHAR2 (80) := 'me@me.com';--REPORT_RECEIVER;
  6.        v_Subject     VARCHAR2 (80) := 'test subject';
  7.        v_Mail_Host   VARCHAR2 (30) := 'smtp.test.com';
  8.        v_Mail_Conn   UTL_SMTP.Connection;
  9.        crlf          VARCHAR2 (2) := CHR (13) || CHR (10);
  10.     BEGIN
  11.  
  12.        /* Define connection */
  13.        v_Mail_Conn := UTL_SMTP.Open_Connection (v_Mail_Host, 25);
  14.        UTL_SMTP.Helo (v_Mail_Conn, v_Mail_Host);
  15.        UTL_SMTP.Mail (v_Mail_Conn, v_From);
  16.        UTL_SMTP.Rcpt (v_Mail_Conn, v_Recipient);
  17.        UTL_SMTP.Data (
  18.           v_Mail_Conn,
  19.              'Date: '
  20.           || TO_CHAR (SYSDATE, 'Dy, DD Mon YYYY hh24:mi:ss')
  21.           || crlf
  22.           || 'From: '
  23.           || v_From
  24.           || crlf
  25.           || 'Subject: '
  26.           || v_Subject
  27.           || crlf
  28.           || 'To: '
  29.           || v_Recipient
  30.           || crlf
  31.           || crlf
  32.           || 'some message text'
  33.           || crlf
  34.           ||                                                       -- Message body
  35.             'more message text'
  36.           || crlf);
  37.        UTL_SMTP.Quit (v_mail_conn);
  38.     EXCEPTION
  39.  
  40.        /* Catch exceptions */
  41.        --WHEN OTHERS THEN         
  42.     END;

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.