Oracle e-mail interface for Windows Oracle Database Tips by Donald Burleson

The following Windows bat file code demonstrates how the Sendmail program is invoked from Windows.  This program accepts the report file name as an argument (for instance a SQL*PLUS spooled file) which it uses in the ?attach parameter of the Sendmail program. The sendmail recipient (TO), sender (FROM), and subject information are set up as variables in the Windows bat file to facilitate the use of the batch program on different servers and in different sites.  In the Windows bat files, the sendmail message text is generated dynamically using echo statements.

Also, see these additional resources for Oracle Windows scripts:

• Dr. Hall has a great collection of downloadable Oracle scripts in his book "Oracle Job Scheduling".
   
• For UNIX and Linux Oracle scripts, see Jon Emmon's book "Oracle Shell Scripts".
   
• For scheduling Windows jobs, see Windows Oracle Job Schedulers examples
   
• For more Oracle Windows scripts, see my notes on Oracle scripts for Windows.

If you want the report file to appear directly within the message text, I could simply eliminate the ?attach parameter and pass the report file to sendmail using the ?messagefile= parameter.  

Sample e-mail script for Oracle Windows

@ECHO OFF

 

REM +-------------------------------------

REM | Set up client specific variables

REM +-------------------------------------

 

set BC_DIR=C:\BC

set FROM=Oracle@Client.com

set TO=Client@remote-dba.net

set SUBJECT=*** Client TRACE ALERT

 

set FILENAME=%1

 

?

?

?

?

REM +-------------------------------------------------------------

REM | Build message text

REM +-------------------------------------------------------------

 

echo. > message.txt

echo 'Please see attached dump identified on' >> message.txt

udate >> message.txt

 

sendmail -messagefile=message.txt -from=%FROM% -subject="%SUBJECT%" -attach=%FILENAME%  %TO%

 

:END

rm -s message.txt

rm -s %FILENAME%

 

exit

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.