How to send email from Oracle database using procedure/PLSQL

January 4, 2017

It has been a long time I did not post to this blog. Happy New Year 2017 anyway!

Recently, I am working on something related to my work. I have to monitor Oracle job scheduler, their job status etc. One of the options came across my mind is to email me the job status everyday from Oracle database.

Below script is working on my machine, which running Oracle XE. If error accrued during execute the script, it may come from insufficient privileges on certain package. Please grant where necessary.

Here is the script:

DECLARE
V_FROM      VARCHAR2(80) := 'YOUR_EMAIL@GMAIL.COM';
V_RECIPIENT VARCHAR2(80) := 'RECIPIENT_EMAIL@GMAIL.COM';
V_SUBJECT   VARCHAR2(80) := 'TEST EMAIL';
V_MAIL_HOST VARCHAR2(30) := 'YOUR_MAIL_HOST';
V_MAIL_CONN UTL_SMTP.CONNECTION;
CRLF        VARCHAR2(2)  := CHR(13)||CHR(10);
V_CONTENT   VARCHAR2(300);

BEGIN
             
    SELECT 'THIS IS EMAIL TESTING FROM ORACLE DATABASE.'  
    INTO V_CONTENT
    FROM DUAL;
                                         
BEGIN
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 ||
  V_CONTENT || CRLF ||      --MESSAGE BODY  
  CRLF        

);
UTL_SMTP.QUIT(V_MAIL_CONN);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
  RAISE_APPLICATION_ERROR(-20000, 'UNABLE TO SEND MAIL', TRUE);
END;
           
END;

Hope this help.