Generate HTML Report SQL*PLUS


#!/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';
<strong>select * from tablename where condition;</strong>
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