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

Using sqlplus, and if u have access to the htp and htf package, it
should be no problem generating HTML, for example, look at the file
$ORACLE_HOME/rdbms/admin/privutil.sql, and search for all the htp.*
function usage, and u can get the idea how it uses htp functions to
generate HTML structure contents.

For example:

htp.preOpen;
htp.prints(translate(text,NL_CHAR,' '));
htp.preClose;

And this:

htp.print('</PRE>');
htp.p(htf.format_cell(columnValue, format_numbers));
htp.tableRowOpen;
htp.tableRowClose;
htp.tableRowOpen;
htp.tableRowClose;
htp.formSelectOpen( cname => p_cname,
htp.formSelectOption( cvalue => nc_visible,
htp.formSelectClose;

etc.

Using sqlplus, and if u have access to the htp and htf package, it
should be no problem generating HTML, for example, look at the file
$ORACLE_HOME/rdbms/admin/privutil.sql, and search for all the htp.*
function usage, and u can get the idea how it uses htp functions to
generate HTML structure contents.

For example:

htp.preOpen;
htp.prints(translate(text,NL_CHAR,' '));
htp.preClose;

And this:

htp.print('</PRE>');
htp.p(htf.format_cell(columnValue, format_numbers));
htp.tableRowOpen;
htp.tableRowClose;
htp.tableRowOpen;
htp.tableRowClose;
htp.formSelectOpen( cname => p_cname,
htp.formSelectOption( cvalue => nc_visible,
htp.formSelectClose;

etc.

Using sqlplus, and if u have access to the htp and htf package, it
should be no problem generating HTML, for example, look at the file
$ORACLE_HOME/rdbms/admin/privutil.sql, and search for all the htp.*
function usage, and u can get the idea how it uses htp functions to
generate HTML structure contents.

For example:

htp.preOpen;
htp.prints(translate(text,NL_CHAR,' '));
htp.preClose;

And this:

htp.print('</PRE>');
htp.p(htf.format_cell(columnValue, format_numbers));
htp.tableRowOpen;
htp.tableRowClose;
htp.tableRowOpen;
htp.tableRowClose;
htp.formSelectOpen( cname => p_cname,
htp.formSelectOption( cvalue => nc_visible,
htp.formSelectClose;

etc.

Using sqlplus, and if u have access to the htp and htf package, it
should be no problem generating HTML, for example, look at the file
$ORACLE_HOME/rdbms/admin/privutil.sql, and search for all the htp.*
function usage, and u can get the idea how it uses htp functions to
generate HTML structure contents.

For example:

htp.preOpen;
htp.prints(translate(text,NL_CHAR,' '));
htp.preClose;

And this:

htp.print('</PRE>');
htp.p(htf.format_cell(columnValue, format_numbers));
htp.tableRowOpen;
htp.tableRowClose;
htp.tableRowOpen;
htp.tableRowClose;
htp.formSelectOpen( cname => p_cname,
htp.formSelectOption( cvalue => nc_visible,
htp.formSelectClose;

etc.