Create_Mail_Blats.sql

Creates two tables: one to store outgoing messages and one history table to store sent messages.

Creates the sequence generator required to give each message a unique ID.

Create a pre-insert trigger to set the sequence number, the "timestamp", and some necessary formatting.

Format_Email.sql

Creates a package that reformats the message by placing end-of-line characters at appropriate intervals to prevent long lines. Some email clients don’t handle long lines very well.

DBMS_Office.sql

Creates a package to replace the routine provided by Oracle’s Interoffice. It accepts the same parameters in the same order. When called, this package inserts the data into the tables described above.

ORA_Mail.bat

This is the main routine. It generates DOS batch files (one for each message). Run this script at appropriate intervals.

Note: Initially, I used the "soon" command found on the NT Resource Kit. Later versions of NT4 service packs include a "Scheduled Tasks" utility that works much better.

Send_Mail.sql

This script generates a temporary batch file (send_mail.bat). Send_Mail.bat contains calls to build_batch.bat passing it several parameters; including email addresses, subject, etc.

Cre_Txt.sql

This scripts generates text files containing the email message. The BLAT routine accepts a DOS file name that represents the body of the outgoing email message.

Build_Batch.bat

This routine creates individual batch files (one for each outgoing email message) using the naming convention of Mail_nnn.bat where nnn represents the sequence number of the email message stored in the mail_blats table.

The last step in ORA_Mail.bat calls each batch filed created by Build_Batch, which send the email via BLAT.

Resend_ID.sql

This script copies a message from the history table and places it back in the outgoing email table. After all, nothing’s perfect and you may need to re-send a message if your SMTP server dies.