HTML reports

[William Robertson]

If I have a report that produces multi-line HTML output, is there a way to view it directly in PL/SQL Developer rather than manually copying it to a file and opening it in the a browser?

I thought I'd write a handy interactive version of the AWR report that Oracle supplies as a SQL*Plus script (awrrpt.sql), which could prompt for inputs with drop-down lists etc, to produce something like this (simplified version):

select output
from   table
       ( dbms_workload_repository.awr_report_html
         ( ( select dbid from v$database )
         , ( select instance_number from v$instance )
         , 309  -- prompts for snap IDs based on user input
         , 310
         )
       );

Some of the other Oracle reports produce CLOBs containing HTML, which is great because I can view them directly in the Large Object Viewer. However dbms_workload_repository.awr_report_html produces a couple of thousand rows of varchar2(1500). If I run my query as a report, it wraps the output in its own HTML, which breaks it. If I run it in a Test window where I could procedurally populate a CLOB, I can't use interactive prompting.

This isn't a big deal, just wondering if I'm missing something.

 

[Marco Kalter]

You can use the Test Window and Oracle's HTP package for this. For example:

begin
  htp.htmlOpen;
  for c in (select output from ...)
  loop
    htp.print(c.output);
  end loop;
  htp.htmlClose;
end;

This will display the result set from the query as HTML on the "HTP Output" page of the Test Window.

 

[William Robertson]

Unfortunately a Test window can't prompt interactively though - only SQL and Report windows can do that, and they can't use OWA output. My idea was to use report variables to populate drop-down lists from dba_hist_snapshot etc rather than hardcoding them as in the simplified example above.

The best I can currently get is to run it in an SQL window, and use the Export Query Results tool (or right-mouseclick) and save as a TSV file with a .html extension, which I can then open in a web browser.

 

[Marco Kalter]

You could create a little function that takes these input parameters (or the entire SQL text), executes the query, and converts the output lines to a CLOB. This function result can be queried from the SQL Window which can then be viewed in the Large Data Editor.

 

[William Robertson]

In theory perhaps. However in the real world I can't release code to production without going through UAT, business approval, signoff etc, so in practice I can't just knock up utility functions when I feel like it.