generate html report using sqlplus

Needed help to generate following report in html using preferably sql*plus leveraging it's built-in html reporting functionality (i.e.set markup html on ) but open for any other solutions as well..

Here is the SQL for the report.

with top_etime_sqls as

   (select sql_id, instance_number inst#, sum(elapsed_time_delta/ 1000000)/greatest(sum(executions_delta),1) etime_secs

    from dba_hist_sqlstat
    where snap_id in (select snap_id from dba_hist_snapshot where begin_interval_time > trunc(sysdate-1))

    group by sql_id, instance_number
    having sum(elapsed_time_delta/1000000)/ greatest(sum(executions_delta),1) > 1000

    )
select tsqls.sql_id, tsqls.inst#, etime_secs, stxt.sql_text , t1.* from dba_hist_sqltext stxt, top_etime_sqls tsqls, table(DBMS_XPLAN.DISPLAY_AWR(tsqls.sql_id)) t1 where stxt.sql_id = tsqls.sql_id
order by 1, 2, 3

Here is how I'm expecting output for above sql.

• sql_id, inst# , etime_secs -- This should be in one html row & 3 html columns.
• complete output of stxt.sql_text for above sql_id should be in next row ( i.e. in one long html column ). It would be nice if it can maintain the format of origional SQL statement from v $sql.sql_fulltext
• Then next rows should have full Explain plan out from dbms_explain.display_awr(v$sql.sql_id) for sql_id above.

again
sql_id, inst#, etime_secs in one html row. complete outout of sql_text in next html row complete explain plan in next html row.

and so on...

Thanks in advance for your help.
-Mak Received on Tue Jan 15 2008 - 15:12:07 CST