This is a script to query a table, generate the result in HTML and send an email using mailx for the report.
#!/bin/ksh . $HOME/.bash_profile NOW=$(date +"%a %b %e %T %Y") MON=$(date +"%b %Y") MON2=$(date +"%b%Y") HOST=$(hostname) cd $HOME/scripts rm -rf Report*.html sqlplus -s /NOLOG << EOF > /tmp/log.txt CONNECT abc/******** set pagesize 1000 set markup HTML ON HEAD "<style type='text/css'> - <title>Card Report</title> - body { - font:10pt Arial,Helvetica,sans-serif; - color:blue; background:white; } - p { - font:8pt Arial,sans-serif; - color:grey; background:white; } - table,tr,td { - font:10pt Arial,Helvetica,sans-serif; - text-align:right; - color:Black; background:white; - padding:0px 0px 0px 0px; margin:0px 0px 0px 0px; } - th { - font:bold 10pt Arial,Helvetica,sans-serif; - color:#336699; - background:#cccc99; - padding:0px 0px 0px 0px;} - h1 { - font:16pt Arial,Helvetica,Geneva,sans-serif; - color:#336699; - background-color:White; - border-bottom:1px solid #cccc99; - margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} - h2 { - font:bold 10pt Arial,Helvetica,Geneva,sans-serif; - color:#336699; - background-color:White; - margin-top:4pt; margin-bottom:0pt;} - a { - font:9pt Arial,Helvetica,sans-serif; - color:#663300; - background:#ffffff; - margin-top:0pt; margin-bottom:0pt; vertical-align:top;} - .threshold-critical { - font:bold 10pt Arial,Helvetica,sans-serif; - color:red; } - .threshold-warning { - font:bold 10pt Arial,Helvetica,sans-serif; - color:orange; } - .threshold-ok { - font:bold 10pt Arial,Helvetica,sans-serif; - color:green; } - </style> - <title>Database Report $MON2</title>" - BODY "<img src=http://www.acme.com/company_logo..gif/>" - TABLE "border='1' width='90%' align='center'" - ENTMAP OFF SPOOL ON spool Report_$MON2.html alter session set nls_date_format = 'DD/MM/YYYY'; select * from tablename where condition; spool off exit EOF cd /export/home/oracle/scripts echo "Report generated on $NOW for Department from $HOST. Use Notepad++ to open CSV file." > /export/home/oracle/scripts/output echo>>output "." echo>>output "." echo>>output "." echo>>output "." echo>>output "." echo>>output "." echo>>output "." echo>>output "." echo>>output "." echo>>output "." echo>>output "." echo>>output "." echo>>output "." echo>>output "." echo>>output "." echo>>output "*** This is an automatically generated message. You have received this message because you are a member of the e-mail notification subscription list for t his server. Please do not reply. ***" (cat /export/home/oracle/scripts/output ; uuencode Report_$MON2.html Report_$MON2.html) | mailx -s "Monthly Report for the Month of $MON " abc@domain.com exit