`
xw302
  • 浏览: 120606 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

sqlplus 生成 html report 并用sendmail发送

htmlsqlplussendmailoraclelinux 
阅读更多
工作需要给领导做汇报，每天自动发送一份邮件，内容需要从db抓取，采用了sqlplus+sendmail(OEL5.3).

1.Sqlplus 生成HTML报表(Report.sql) 
spool /home/oracle/AUTOMAIL/Report.html;//设置生成文件内容
set term on; //在终端显示运行过程
set linesize 1000; //每行的字符数，设置稍微大些，避免换行
set underline off;
set verify off;
set echo off;  //不把输出的结果显示在屏幕上
set feedback off; //回显本次sql命令处理的记录条数，缺省为on
set heading off;   //输出域标题，缺省为on
set pagesize 100; //页面行数
set trimspool on; //删除行后面的空格，如果不设置，在email中显示会有'!'
set trimout on; //删除行后面的空格
//附件是这些参数的官方文档

//html head
SELECT '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head>
<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
<title>Summary Report</title></head>' FROM DUAL;


select '<center><br><b>Your query would result in' from dual;
SELECT
  count(*)
FROM tableA
WHERE 
  status >'1';
select 'records.</b><br></center>' from dual;


//css
select '<style type="text/css">' from dual;

select 'table {border-width: 1px 1px 1px 1px;border-spacing: 1px 3px 1px 3px;border-style: solid solid solidsolid;border-color: grey grey grey grey;border-collapse: collapse;}' from dual;

select 'table th {border-width: 1px 1px 1px 1px;border-style: solid solid solid solid;border-color: grey grey grey grey;font-size: 12px;-moz-border-radius: 0px 0px 0px 0px;}' from dual;

select 'table tr:hover {color: darkblue;}' from dual;

select 'table td {border-width: 1px 1px 1px 1px;padding: 1px 4px 1px 4px;border-style: solid solid solid solid;border-color: gray gray gray gray;text-align: left;font-size: 12px;-moz-border-radius: 0px 0px 0px 0px;}' from dual;

select 'table.nested {border-width: 0px 0px 0px 0px;border-spacing: 1px 3px 1px 3px;border-color: white white white white;}' from dual;

select 'table.nested tr:hover {color: darkblue;}' from dual;

select 'table.nested td {border-width: 0px 0px 0px 0px;padding: 1px 4px 1px 4px;border-color: white white white white;text-align: left;font-size: 10px;-moz-border-radius: 0px 0px 0px 0px;}' from dual;

select 'table.nolines {border-width: 0px 0px 0px 0px;border-spacing: 1px 3px 1px 3px;border-color: white white white white;}' from dual;

select 'table.nolines td {border-width: 0px 0px 0px 0px;padding: 1px 4px 1px 4px;border-color: white white white white;text-align: center;font-size: 12px;-moz-border-radius: 0px 0px 0px 0px;}' from dual;

select 'table.nolinesLeftAligned {border-width: 0px 0px 0px 0px;border-spacing: 1px 3px 1px 3px; border-color: white white white white;}' from dual;

select 'table.nolinesLeftAligned tr:hover {color: black;font-size: 14px;}' from dual;

select 'table.nolinesLeftAligned td {border-width: 0px 0px 0px 0px;padding: 1px 4px 1px 4px;   border-color: white white white white;text-align: left;font-size: 12px;font-face: Arial;-moz-border-radius: 0px 0px 0px 0px;}' from dual;

select '</style>' from dual;

//table
select '<h2 align="center"></h2>
<center>
<b>Summary Report</b>
<br>
<span id="selectedbugs">(400 records)</span>
</center>' from dual;

select '<table id="SummaryTab" class="sortable"><thead><tr style="font-family: Arial,Helvetica,Geneva,sans-serif; font-size: 10pt; font-weight: bold; text-align: center; background-color: rgb(204, 204, 153); color: rgb(51, 102, 153);">'
|| '<th>No.</th>'
|| '<th>name</th>'
|| '<th>salaries</th>'
|| '<th>department</th>'
|| '</tr></thead>' from dual;

select 
'<tr style="font-family: Arial,Helvetica,Geneva,sans-serif; font-size: 9pt;"><td>' || rownum ||'</td>',
'<td><a href=https://wwww.g.cn?no=' || '&' || 'no=' || no || '>' || rptno || '</a></td>',
'<td>' || name || '</td>',
'<td>' || salaries || '</td>',
'<td>' || department || '</td></tr>'
from 
(
select h.no,h.name ,h.salaries ,h.department
from
tableA h 
WHERE 
  h.product_id =xxxx 
  order by h.no asc, h.name desc
)
  where rownum <= 400;

spool off;




2.执行report.sql并发送邮件(automail.sh)--用crontab调用并定时发送即可 
#warning,not edit or delete the content
#!bin/sh/

#script folder path
BurReportPage="/home/oracle/AUTOMAIL"

cd $BurReportPage

#connect bugDB and run sql to get date
source /home/oracle/db.env
sqlplus username/psword@servanme/sid <<EOF
@Report.sql
exit
EOF

#send mail 
cat ./Report.html|formail -I "From: mailname@x.com" -I "MIME-Version:1.0" -I "Content-type:text/html;charset=utf-8" -I "Subject:Daily Reports Automail" -I "To:abama@x.com" -I "Cc:abamb@x.com"|/usr/sbin/sendmail -oi -t


分享到：
| 健康生活
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics