Every DBA knows that: SQL*Plus is a powerful tool, but its output is often almost unreadable. Do we have to format everything carefully with col commands or change to a more comfortable GUI instead? No! The early Tanel Poder has shown an easy way to get nice HTML output from SQL*Plus back in 2007 already:
I have adopted this idea and use it in my courses (also this week) whenever I need to do those queries on broad views like DBA_TABLESPACES or V$DATAFILE. I just run them in SQL*Plus as usual; if the output is unreadable, I call html.sql (First the SELECT, then simply @html), firefox opens and shows us a pretty looking HTML output, everybody is happy and the audience is stunned. Thank you, Tanel!
This is the adopted, slightly customized html.sql I use:
---------------------------------------- -- get the last SQL*Plus output in HTML -- after Tanel Poder ---------------------------------------- set termout off set markup HTML ON HEAD " - - " - BODY "" - TABLE "border='1' align='center' summary='Script output'" - SPOOL ON ENTMAP ON PREFORMAT OFF spool myoutput.html l / spool off set markup html off spool off host firefox myoutput.html set termout on
Give it a try, it is really simple and much better readable then. Reminds us at the deprecated iSQL*Plus, doesn’t it?
#1 by Walter on June 30, 2011 - 23:45
That’s neat! To add a nice little touch so your window is freed up, modify the line
>>host firefox myoutput.html
host start firefox myoutput.html
and you get your prompt back
#2 by Walter on June 30, 2011 - 23:48
Forgot to mention, the above command is if you are on windows.
host start… ..
#3 by Ittichai Chammavanijakul on July 1, 2011 - 00:22
I’ve been using markups in multiple SQL reports which will be emailed to managers for ease of reading data. I blogged about it awhile back at http://oraexplorer.com/2008/12/generating-html-reports-from-sql-plus/ The nice thing is that I can use CSS classes for custom text formatting as well as embed images. Thanks.
#4 by Laurent Schneider on July 1, 2011 - 11:16
Any change to get valid html?
prom hello worldhello world
ERROR: end tag for “br” omitted, but OMITTAG NO was specified
<BR>
You may have neglected to close an element, or perhaps you meant to “self-close” an element, that is, ending it with “/>” instead of “>”.
#5 by Laurent Schneider on July 1, 2011 - 11:17
prom hello world
<br>
hello world
<br>
#6 by Uwe Hesse on July 1, 2011 - 12:30
I am sorry, Laurent, but I fail to see what you are trying to tell me.
#7 by Uwe Hesse on July 1, 2011 - 12:38
Ittichai, thank you for sharing your knowledge here about generating HTML reports from SQL. They look really impressive
#8 by Laurent Schneider on July 1, 2011 - 13:52
Hi Uwe,
<br>
hello world
<br>
Is not valid html.
A BR tag should be either <br>blabla</br> or <br/>blabla<br/> because in standard HTML formating the <br> tags are not closed
#9 by Uwe Hesse on July 1, 2011 - 15:06
Laurent, I see. But I did not say “hello world” nor did I mention BR anywhere in the posting, did I? Do you mean that the script I show in the posting does not work? Then all I can say is: It works for me
#10 by Laurent Schneider on July 1, 2011 - 18:04
if you do
select dummy from dual;
<br>
<p>
<table border='1' width='90%' align='center' summary='Script output'>
<tr>
<th scope="col">
D
</th>
</tr>
<tr>
<td>
X
</td>
</tr>
</table>
<p>
You will get very old-fashion html code,where the <p> and <br> tags are not closed. Nowaday, most HTML generators produced code with <p>hello</p> or <br/>
You see what I mean?
I would love to see sqlplus producing XHTML code, that could be read with an XML parser
#11 by Uwe Hesse on July 1, 2011 - 20:26
Got your point now, Laurent
#12 by Michaelb@nams.net on October 12, 2011 - 20:40
Thank you! This is great for queries that you just don’t want to format in your sqlplus session. While not used daily it will helpful when needed.