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
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.