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.