sqlplus 生成 html report 并用sendmail发送

工作需要给领导做汇报，每天自动发送一份邮件，内容需要从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

SET_System_Variable_Summary.zip (25.9 KB)
下载次数: 0

Tags：