Generating HTML Reports from SQL*Plus

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

 

Category: DatabaseSQL

Tags:

Leave a Reply

Article by: Shadab Mohammad