Tag: 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

PROCEDURE EMAIL_ERROR_REPORT IS
       
       /* Create vars */
       v_From        VARCHAR2 (80) := 'test@test.com';
       v_Recipient   VARCHAR2 (80) := 'me@me.com';--REPORT_RECEIVER;
       v_Subject     VARCHAR2 (80) := 'test subject';
       v_Mail_Host   VARCHAR2 (30) := 'smtp.test.com';
       v_Mail_Conn   UTL_SMTP.Connection;
       crlf          VARCHAR2 (2) := CHR (13) || CHR (10);
    BEGIN

       /* Define connection */
       v_Mail_Conn := UTL_SMTP.Open_Connection (v_Mail_Host, 25);
       UTL_SMTP.Helo (v_Mail_Conn, v_Mail_Host);
       UTL_SMTP.Mail (v_Mail_Conn, v_From);
       UTL_SMTP.Rcpt (v_Mail_Conn, v_Recipient);
       UTL_SMTP.Data (
          v_Mail_Conn,
             'Date: '
          || TO_CHAR (SYSDATE, 'Dy, DD Mon YYYY hh24:mi:ss')
          || crlf
          || 'From: '
          || v_From
          || crlf
          || 'Subject: '
          || v_Subject
          || crlf
          || 'To: '
          || v_Recipient
          || crlf
          || crlf
          || 'some message text'
          || crlf
          ||                                                       -- Message body
            'more message text'
          || crlf);
       UTL_SMTP.Quit (v_mail_conn);
    EXCEPTION

       /* Catch exceptions */
       --WHEN OTHERS THEN         
    END;