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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
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;

Leave a Reply