Experimenting with Automated Emails

July 7, 2010

I have not had much of a need to send emails on a scheduled basis using Oracle Database.  Roughly nine years ago I created a program that directly interacts with an SMTP server to send HTML formatted emails on a schedule.  If I recall correctly, we have over 200 such emails based on data contained in the database that are sent on schedule.  In my custom program there are several built-in custom reports, but I am also able to build a report directly from a SQL statement.  The custom program looks like this:

The {TODAY} keyword displayed in the Alternate Report Title box is one of several that may be specified in the subject line or in the Custom SQL Statement by right-clicking (click to see the large version of this menu):

—–

A request recently came in from an ERP mailing list that looked something like this (paraphrased):

I want an email sent when the expiry_date = trunc(sysdate) +30, so even if the vendor does not supply to us in a specific time frame, we can alert him, and the relevant staff, that his supplier certification is about to expire.

The original poster was looking for a way to automatically send an email to a supplier when some sort of expiration date was approaching, and of course a trigger could not send the email if there was no triggering event.  The original poster provided the following code sample:

DECLARE
msg varchar2(20000) := 'TESTING ONLY';
recipient varchar2(20000);
BEGIN
select EMAIL_id into recipient from vw_vendor_a_email;
UTL_MAIL.SEND (
sender => 'dontspamme@mycompany.com.au',
recipients => recipient,
subject => 'Documents have been attached to ',
message => msg||' Please update your documents');
END;
/

The OP was happy with the code sample, but was having trouble when the SQL statement returned more than one email address (SELECT … INTO MY_VARIABLE only allows a single row to be returned).  There are a couple of methods to solve this problem/limitation – one of those methods is shown below.

First, we should probably make certain that the SMTP_OUT_SERVER parameter value is specified:

SHOW PARAMETER SMTP_OUT_SERVER

NAME               TYPE        VALUE
------------------ ----------- ------------------------
smtp_out_server    string      mailman.mycompany.com.au

Next, we should probably create a test table with a couple of email addresses:

CREATE TABLE T1(
  VENDOR_ID VARCHAR2(15),
  EMAIL_ADDRESS VARCHAR2(50));

INSERT INTO T1 VALUES(
  'INTERNAL',
  'tryme@mycompany.com.au');

INSERT INTO T1 VALUES(
  'INTERNAL',
  'headache@mycompany.com.au');

INSERT INTO T1 VALUES(
  'INTERNAL',
  'helpme@mycompany.com.au');

COMMIT;

Now we may construct a SQL statement to put the email addresses into a format that is suitable for SMTP email servers (only tested with Microsoft Exchange):

SELECT
  MAX(SUBSTR(SYS_CONNECT_BY_PATH(EMAIL_ADDRESS,';'),2))
FROM
  (SELECT
    EMAIL_ADDRESS,
    ROWNUM RN
  FROM
    T1
  WHERE
    VENDOR_ID='INTERNAL'
  ORDER BY
    EMAIL_ADDRESS)
CONNECT BY
  PRIOR RN=RN-1
START WITH
  RN=1;

MAX(SUBSTR(SYS_CONNECT_BY_PATH(EMAIL_ADDRESS,';'),2))
------------------------------------------------------------------------
tryme@mycompany.com.au;headache@mycompany.com.au;helpme@mycompany.com.au

Note that the email addresses are not in alphabetical order even though there is an ORDER BY clause in the inline view – that is because the ORDER BY clause is applied after the ROWNUM value is calculated.  We could fix that, if it were important.  The above email address list is in the correct format for Microsoft Exchange.

Now let’s put that SQL statement into the PL/SQL script supplied by the OP:

DECLARE
msg varchar2(20000) := 'TESTING ONLY';
recipient varchar2(20000);
BEGIN
SELECT
  MAX(SUBSTR(SYS_CONNECT_BY_PATH(EMAIL_ADDRESS,';'),2)) into recipient
FROM
  (SELECT
    EMAIL_ADDRESS,
    ROWNUM RN
  FROM
    T1
  WHERE
    VENDOR_ID='INTERNAL'
  ORDER BY
    EMAIL_ADDRESS)
CONNECT BY
  PRIOR RN=RN-1
START WITH
  RN=1;
UTL_MAIL.SEND (
sender => 'dontspamme@mycompany.com.au',
recipients => recipient,
subject => 'Documents have been attached to ',
message => msg||' Please update your documents');
END;
/

Note that when the above anonymous PL/SQL script is executed, the email server may return an error message if we had inserted an email address into table T1 that is foreign (belonging to a different email address domain).  For instance, Microsoft Exchange might return a 550 5.7.1 Unable to relay error if we tried to send an email to Bill Gates:

ERROR at line 1:
ORA-29279: SMTP permanent error: 550 5.7.1 Unable to relay
ORA-06512: at "SYS.UTL_SMTP", line 21
ORA-06512: at "SYS.UTL_SMTP", line 99
ORA-06512: at "SYS.UTL_SMTP", line 241
ORA-06512: at "SYS.UTL_MAIL", line 414
ORA-06512: at "SYS.UTL_MAIL", line 602
ORA-06512: at line 19

In such a case, the email administrator would need to adjust a couple of security settings.

Now what do we do?  We need the email to be sent on a scheduled basis.  We will use the DBA 2.0 approach to schedule the execution of our anonymous PL/SQL.  Fire up Enterprise Manager Database Control and connect as a user with permissions to create scheduled jobs.  Then we just walk through the steps and fill in the blanks:

—

And for the people who still want to do things the DBA 1.0 way, Enterprise Manager Database Control will show you the SQL statement that may be used in SQL*Plus:

—–

How would you answer the original poster’s question?  What changes would you make to my suggestions?  Is there a much better way to send the emails?