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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s