Sending emails... from the Oracle database

ELT tools like Oracle Warehouse Builder are poor of predefined components for integrating the Data Warehouse with the rest of the world.

For istance, suppose that you are using Oracle OWB and your ETL/Data Integration process is required to notify you  -or the system administrator- in case of ETL process failures: well, you don´t have a predefined component to send emails.

Fortunately Oracle offers us the possibilty to directly send emails, with or withoout attachments, from "inside" the database. We just have to build a piece of PL/SQL code.

First of all, we need to connect as database administrator, in order to set the outgoing smtp server:

CONN / AS SYSDBA;ALTER SYSTEM SET smtp_out_server = 'YOURSMTPSERVER' SCOPE=BOTH;

Using a normal user, we can then create an anonymous block in order to test the functioning of the UTL_MAIL package:

BEGIN  dbms_output.put_line('UML MAIL SEND TEST');    UTL_MAIL.SEND   ( sender      => 'ADMINISTRATORNAME' , recipients  =>  'ADMINISTRATOREMAIL', --, cc          IN    VARCHAR2 CHARACTER SET ANY_CS DEFAULT       NULL, , bcc         => 'YOUREMAIL', , subject     => 'Test email from Oracle', , message     =>  'This comes from the Oracle Server.' --, mime_type   IN    VARCHAR2 DEFAULT 'text/plain; charset=us-ascii', --, priority    IN    PLS_INTEGER DEFAULT NULL);  )  ;END;/

If we wanna automate the process in order to send more than one email, we can manually encode a solution like this:

CREATE OR REPLACE PROCEDURE   mul_email_send IS  CURSOR  mcur  IS SELECT  EMAILCOLUMN FROM  EMAILSTABLE WHERE EMAILCOLUMN IS NOT NULL; v_sender VARCHAR2(30) := 'ADMINISTRATORNAME'; v_sender_email VARCHAR2(30) := 'ADMINISTRATOREMAIL'; v_subject   VARCHAR2(50) := 'YOURSUBJECT'; v_message   VARCHAR2(4000) := YOURMESSAGE; v_mime_type  VARCHAR2(30) := 'text/plain; charset=us-ascii';BEGIN   SELECT  EMAILCOLUMN   INTO  v_subject   FROM  EMAILTABLE;   FOR  mrec    IN  mcur   LOOP      utl_mail.send(v_sender, v_sender_email, NULL, mrec, v_subject, v_message, v_mime_type, NULL);   END LOOP;END mul_email_send;/

This solution works but it's not elegant neither clean, and should be used only in case your ELT tool doens't offer any alternative. Hand-coding a Data Warehouse (as well as many other systems and solutions) makes it too hard to scale and, makes the data integration process too inaccurate or even incomplete.

In addition, the entire enterprise risks to become too dependent on the specific style of its developers.