Email Notifications - Making UTL_MAIL Work

Email Notifications - Making UTL_MAIL Work

If it is IMMEDIATE or DEFERRED then you can use an update statement:

 

ALTER SYSTEM SET smtp_out_server = 'exchange.yourсompany.com:25' SCOPE=BOTH;

If it is FALSE then you need to use a SPFILE and then reboot the databse:

ALTER SYSTEM SET smtp_out_server = 'exchange.yourсompany.com:25' SCOPE=SPFILE;

Now you need to do is Grant the user who will be doing the sending (EN1103, CS_NOTEBOOK9 for example) the ability to use it:

GRANT execute ON utl_mail TO <schema_name>;

 

Below is some example code of how to send HTML formatted table data via email.  This is just an example so you can see how to do the formatting.

 

Something like this could be put into a proceedure where the email address was passed in and then called from a job, like sending warnings when users have not closed their "Open" Experiments in their Notebooks after a certain period of time.

 

DECLARE

v_Message clob;
v_line varchar(4000);

Cursor Cur is select primary_key, name, storage_prog_id from eln_field_types;
rec cur%rowtype;

BEGIN

    v_Message :=
    q'# <p><b>This is a summary of your ELN_FIELD_TYPES table</b></p>
    <p></p>
    <p>Please follow up accordingly<p>
    <p><p>
    <table border=1 width="100%">
      <tr>
      <th align="left" bgcolor="#C0C0C0" bordercolor="#FFFFFF">PRIMARY_KEY</th>
      <th align="left" bgcolor="#C0C0C0" bordercolor="#FFFFFF">NAME</th>
      <th align="left" bgcolor="#C0C0C0" bordercolor="#FFFFFF">STORAGE_PROG_ID</th>
      </tr>
    #';

    OPEN cur; -- open the cursor before fetching
    LOOP
        FETCH cur INTO rec;
        EXIT WHEN cur%NOTFOUND;
       
        select null into v_line from dual;
       
      
        select '<tr> '||  
            '<td nowrap>'||rec.primary_key||'</td>'||
            '<td nowrap>'||Rec.name||'</td>'||
            '<td nowrap>'||rec.storage_prog_id||'</td>'||
         ' </tr>' into v_line from dual;
        
         select v_message||v_line into v_message from dual;
        
    end loop;
    close cur;
   
    select v_message||'</table>' into v_message from dual;

UTL_MAIL.SEND(
sender=>'abc@yourсompany.com',
recipients=>'abc@yourсompany.com',
subject=>'This is your fields table',
message => v_Message,
priority => 1, -- 1-> Hight
mime_type => 'text/html; charset=us-ascii'); --send out emails in HTML format.

END;

/

And the email that came to the user (so you can see the formatting of how it really looks):

 

 

From: abc@yourcompany.com [mailto:abc@yourcompany.com]
Sent: Saturday, December 06, 2008 3:13 PM
To: User ABC
Subject: This is your fields table
Importance: High

 

This is a summary of your ELN_FIELD_TYPES table

Please follow up accordingly

PRIMARY_KEY	NAME	STORAGE_PROG_ID
1	Chemical Structure	ENStandard9.ChemicalStructureFactory
2	Property List	ENStandard9.PropertyListFactory
3	Property Query	ENStandard9.PropertyQueryField
4	Table	ENStandard9.TableFactory
5	Styled Text	ENStandard9.StyledTextFactory
6	Active Document	ENStandard9.MSWordDocumentField
7	Spectrum	ENStandard9.SpectrumFactory
8	Query Text	ENStandard9.QueryTextFactory
9	Database Table	ENStandard9.DBTableField
10	Stored Document	ENStandard9.DocumentFactory
11	Chemical Query	ENStandard9.ChemicalQueryFactory
12	Table of Contents	ENStandard9.DBTableField
13	Search Location	ENStandard9.SearchLocationField
14	Unannotated Version Query	ENSearchEngine9.UnannotatedQueryField
15	Table Query	ENStandard9.TableQueryField
26	URL Display	ENStandard9.URLDisplayField
27	Subsection	ENStandard9.SubsectionFactory
30	Collection Query	ENSearchEngine9.CollectionQueryFactory
31	State Query	ENSearchEngine9.StateQueryField
32	Collection Type Query	ENSearchEngine9.CollectionTypeField
33	Ancillary Data	ENStandard9.AncillaryDataField
34	Database Value	ENStandard9.DBValueField
35	Database Table Filter	ENStandard9.DBTableFilter
36	Excel Spreadsheet	ENStandard9.MSExcelDocumentField
37	Join Type	ENSearchEngine9.JoinTypeField

 

More information:

http://www.oracle.com/technology/products/oem/pdf/BestPractices-Notifications.pdf