Sqlplus HTML reports

I was working on setting up monitoring for application team to monitor AQ and wanted html formatting of table data. On searching I found this link from Ittichai Chammavanijakul  which makes use of css formatting to generate good visual reports.

I used following code

set markup HTML ON HEAD "<style type='text/css'> -
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>SQL*Plus Report</title>" -
BODY "" - 
TABLE "border='1' width='90%' align='center'" -
ENTMAP OFF SPOOL ON

So you can execute above code in sqlplus and spool the output to file. This file can be send using uuencode/sendmail function . You can use below code (note content-type is text/html)

_send_email()
{
/usr/lib/sendmail -t << EOF
From: $FROMADDRESS
To: $EMAIL
Subject: $MESSAGE
X-Priority: 1
Content-type: text/html

`cat $LOGFILE |grep -v 'rows selected'`
EOF
}

Call the function _send_email in your script and ensure all variables are correctly set.

uuencode usage is also simple

uuencode emp.html emp.html|mailx [email protected]

Tags: 10g11ghtmlsqlplus