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命令处理的记录条数，缺省为onset heading off; //输出域标题，缺省为onset pagesize 100; //页面行数set trimspool on; //删除行后面的空格，如果不设置，在email中显示会有'!'set trimout on; //删除行后面的空格//附件是这些参数的官方文档//html headSELECT '<!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;SELECTcount(*)FROM tableAWHERE status >'1';select 'records.</b><br></center>' from dual;//cssselect '<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;//tableselect '<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.departmentfromtableA 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 pathBurReportPage="/home/oracle/AUTOMAIL"cd $BurReportPage#connect bugDB and run sql to get datesource /home/oracle/db.envsqlplus username/psword@servanme/sid <<EOF@Report.sqlexitEOF#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
页:
[1]