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