Hi All.
Here the bash script for sending sql query results as HTML body using SENDMAIL.
#!/bin/sh
You will need to setup the environment for oracle here:
ORACLE_SID=prddb;
ORAENV_ASK=NO;
. /usr/local/bin/oraenv;
You will need to configure the following script variables:
# variables
EMAIL_LIST=”you@yourdomain.com";
LOG_FILE="/k01/app/oracle/tmp/htmlwithsqlplus.log";
SUBJECT="HTML With SQLPlus";
SENDMAIL="/usr/sbin/sendmail";
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;
EOF
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
${SENDMAIL} ${EMAIL_LIST} < ${LOG_FILE};
Good Luck...
No comments:
Post a Comment