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.
#13 by Nafiz on November 3, 2013 - 07:59
Much thanks to Uwe Hesse for this great article on how to transform a SQL output into a GUI HTML-based format.i have just tried it out and it works fine.thanks again.
I just have this one little question,how do i remove the query part and keep the output result only on my browser?
cheers.
– Nafiz
#14 by http://google.com/ on January 3, 2014 - 18:10
My family members every time say that I am killing my time here at net, however I know I am getting experience
every day by reading such pleasant articles.
#15 by bharath. Panuganti on March 10, 2014 - 12:13
HI,
As indicated above, Tried (First the SELECT, then simply @html) but no luck, As its been lashing with below err. Please check on and let me know the get away from this err
SQL> @html
SP2-0310: unable to open file “html.sql”
SQL> @htmlrun “select * from v$session”
SP2-0310: unable to open file “htmlrun.sql”
#16 by Uwe Hesse on March 10, 2014 - 12:50
The script works for me exactly as in the posting. Just checked it again with copy & paste from above and running on Oracle Linux 2.6.39-400.17.1.el6uek.x86_64 It is called by @html only. Maybe you have specified a certain folder for SQL*Plus to look after files to run. Then try to use the full filename in the script.
#17 by bharath. Panuganti on March 11, 2014 - 11:58
Uwe, I’m afraid I haven’t used any of the script spell out in above, All I did was mere execution of select * from v$session and thereafter @html at sql prompt. Kindly let me know is there any folder that need to be specified…or running the above given scripts as it is is a must before executing any sql followed by @html
#18 by Sagar K N on May 23, 2014 - 09:50
Hi does this works in Oracle (Toad)? it’s not working for me.
#19 by Antonio on July 23, 2014 - 20:41
Hello
What if the resulting report has more than 15 rows?! what happens to me is that when reaching row 15 it shows the headers again and continue with the output, don’t really know why
#20 by laurentschneider on July 23, 2014 - 22:55
set pages 50000
is the max, one header every 50000 lines. or set head off for no header