How to send email from Oracle database using procedure/PLSQL

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.

Oracle Database Express Edition 11g Release 2 not work for 64bit OS?

Oracle Database 11g Express Edition (Oracle Database XE) is an entry-level, small-footprint database based on the Oracle Database 11g Release 2 code base.  

It’s free to develop, deploy, and distribute; fast to download; and simple to administer.

 

Oracle Database XE is a great starter database for:

 

  • Developers working on PHP, Java, .NET, XML, and Open Source applications
  • DBAs who need a free, starter database for training and deployment
  • Independent Software Vendors (ISVs) and hardware vendors who want a starter database to distribute free of charge
  • Educational institutions and students who need a free database for their curriculum

 

With Oracle Database XE, you can now develop and deploy applications with a powerful, proven, industry-leading infrastructure, and then upgrade when necessary without costly and complex migrations.

 

Oracle Database XE can be installed on any size host machine with any number of CPUs (one database per machine), but XE will store up to 11GB of user data, use up to 1GB of memory, and use one CPU on the host machine.

 

But, it also has a limitation. This XE does not offer to 64bit Operating System. I was wondering why? Perhaps it is due to Oracle strategy in terms of sales, which at the end will bring user to choose a upper version of Oracle database, Enterprise edition.

 

Well, it make sense. If Oracel to offer everything for free. How are they going to make money from their Oracle licensing revenue.

 

However, some information from the internet has say that Oracle Database XE can be install in 64bit operating machine, somehow. Well, I am about to test it and see how true the statement was.

 

See you in my next post about this.

 

If you wanted to know more about Oracle Database XE, you can go to Oracle website here.