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.
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
|---|---|---|---|---|---|---|---|
| 7369 | SMITH | CLERK | 7902 | 17-DEC-1980 00:00:00 | 800 | 20 | |
| 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-1981 00:00:00 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 22-FEB-1981 00:00:00 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 02-APR-1981 00:00:00 | 2975 | 20 | |
| 7654 | MARTIN | SALESMAN | 7698 | 28-SEP-1981 00:00:00 | 1250 | 1400 | 30 |
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]
Recent Comments