Friday, May 13, 2011

Sendmail : Add HTML to mail body.

Hi All.

Here the bash script for sending sql query results as HTML body using SENDMAIL.

You will need to setup the environment for oracle here:
. /usr/local/bin/oraenv;

You will need to configure the following script variables:
# variables
You really don’t need to edit anything past the variables, unless you are familiar with HTML and sqlplus formatting options. Having a little bit of HTML knowledge could be using in making the emails more appealing.
The following creates the email header:
# create header
echo "To: ${EMAIL_LIST}" > ${LOG_FILE};
echo "Subject: ${SUBJECT}" >> ${LOG_FILE};
echo "Content-Type: text/html; charset=\"us-ascii\"" >> ${LOG_FILE};
echo "" >> ${LOG_FILE};
The following creates the start of the HTML content:
# message Starts
echo "<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">" >> ${LOG_FILE};
echo "<html>" >> ${LOG_FILE};
echo "<head>" >> ${LOG_FILE};
echo "<title></title>" >> ${LOG_FILE};
echo "</head>" >> ${LOG_FILE};
echo "<body bgcolor=\"#ffffff\" text=\"#000000\">" >> ${LOG_FILE};
Now it's time for the output of sqlplus:
# message body
sqlplus -s "/ as sysdba" >> ${LOG_FILE} <<EOF
set markup html on
set feedback off
select * from dictionary order by table_name;

If you have an object with a $ sign in the name you will need to escape it. For example, v$database would be v\$database.
The following ends the HTML content:
# finish message
echo "</body>" >> ${LOG_FILE};
echo "</html>" >> ${LOG_FILE};
The following sends the message:
# mail the logfile results

Good Luck...

No comments:

Post a Comment