sqlplus里运行脚本生成类似awr样式的报告模板

3reef

oracle中可以使用sqlplus的set markup html ，生成html格式的报告，虽然一些css样式可以从awr报告的源代码中获得，但是一直想模拟awr报告那种隔行换色的效果一直没找到简易的方法来弄，尤其像我这种不会html编程的，今天才知道js可以放在body里，总算解决了一个我长久以来的困扰，以下是一个生产最近24小时awr报告index的html页面，有这个模板，基本上只要把sql语句往里扔就可以生成一个山寨awr样式的报告了。

set linesize 200
set term off verify off feedback off pagesize 999
rem set markup html on entmap ON spool on preformat off
set trims on;
COLUMN instname NEW_VALUE _instname NOPRINT
select instance_name instname from v$instance;
COLUMN indextime NEW_VALUE _indextime NOPRINT
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') indextime from dual;
COLUMN rname NEW_VALUE _rname NOPRINT
select instance_name||'_awr_' rname from v$instance;
set heading on
set markup html on spool on preformat off entmap on -
head "<title>Database Report</title> -
  <style type='text/css'> -
   body              {font:bold 10pt Arial,Helvetica,Geneva,sans-serif;color:black; background:White;} -
   p                 {font:8pt Courier;color:black; background:White;} -
   td                {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;vertical-align:top;align:left;} -
   th                {font:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:White; background:#0066CC;padding-left:4px; padding-right:4px;padding-bottom:2px} -
   h1                {font:bold 20pt 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 18pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;margin-top:4pt; margin-bottom:0pt;} -
   h3                {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background-color:White;margin-top:4pt; margin-bottom:0pt;} -
   a                 {font:9pt Arial,Helvetica,sans-serif; color:#663300; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
   a.link            {font:9pt Arial,Helvetica,sans-serif; color:#663300; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
   a.noLink          {font:bold 8pt Arial,Helvetica,sans-serif;color:#663300; vertical-align:top;margin-top:0pt; margin-bottom:0pt;} -
   a.noLinkBlue      {font:9pt Arial,Helvetica,sans-serif; color:#0000ff; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
   a.noLinkDarkBlue  {font:9pt Arial,Helvetica,sans-serif; color:#000099; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
   a.noLinkRed       {font:9pt Arial,Helvetica,sans-serif; color:#ff0000; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
   a.noLinkDarkRed   {font:9pt Arial,Helvetica,sans-serif; color:#990000; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
   a.noLinkGreen     {font:9pt Arial,Helvetica,sans-serif; color:#00ff00; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
   a.noLinkDarkGreen {font:9pt Arial,Helvetica,sans-serif; color:#009900; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
  </style>" -
body   "BGCOLOR='#0066CC'" -
table  "WIDTH='600px' BORDER='1' id='tab1'"
spool /check/awr/index.html
set markup html on entmap off
prompt <H1 size=+3>Report of &_instname. instance</H1><H3>the lastest report is generated at &_indextime. by liaody</H3>


select
snap_id,
'<a href="&_rname.'||to_char(end_interval_time,'yyyymmdd_hh24mi')||'.html">&_rname.'||to_char(end_interval_time,'yyyymmdd_hh24mi')||'.html </a>' report,
to_char(begin_interval_time,'YYYY-MM-DD HH24:MI') begin_time ,
to_char(end_interval_time,'YYYY-MM-DD HH24:MI') end_time
from
(
select * from dba_hist_snapshot
order by snap_id desc
)
where rownum<=24;

prompt <font size="2" face="Arial,Helvetica,Geneva,sans-serif"><b>End of Report</b></font>
prompt <script> -
        var table =document.getElementById("tab1"); -
    var len = table.rows.length; -
    for(var i=0;i<len;i++){ -
                if(i%2 == 0){ -
                        table.rows[i].style.background = "#FFFFCC"; -
                }else{ -
                        table.rows[i].style.background = "#FFFFFF"; -
                } -
        } -
</script>
spool off
SET MARKUP HTML OFF
SET TERMOUT ON
prompt
EXIT;