Experimenting with Automated Emails

7072010

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:

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:

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:

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?

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: