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?
Pickleball spielen 
#1 von Walter am Juni 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 von Walter am Juni 30, 2011 - 23:48
Forgot to mention, the above command is if you are on windows.
host start… ..
#3 von Ittichai Chammavanijakul am Juli 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 von Laurent Schneider am Juli 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 von Laurent Schneider am Juli 1, 2011 - 11:17
prom hello world
<br>
hello world
<br>
#6 von Uwe Hesse am Juli 1, 2011 - 12:30
I am sorry, Laurent, but I fail to see what you are trying to tell me.
#7 von Uwe Hesse am Juli 1, 2011 - 12:38
Ittichai, thank you for sharing your knowledge here about generating HTML reports from SQL. They look really impressive 🙂
#8 von Laurent Schneider am Juli 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 von Uwe Hesse am Juli 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 von Laurent Schneider am Juli 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 von Uwe Hesse am Juli 1, 2011 - 20:26
Got your point now, Laurent 🙂
#12 von Michaelb@nams.net am Oktober 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 von Nafiz am 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 von http://google.com/ am Januar 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 von bharath. Panuganti am März 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 von Uwe Hesse am März 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 von bharath. Panuganti am März 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 von Sagar K N am Mai 23, 2014 - 09:50
Hi does this works in Oracle (Toad)? it’s not working for me.
#19 von Antonio am Juli 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 von laurentschneider am Juli 23, 2014 - 22:55
set pages 50000
is the max, one header every 50000 lines. or set head off for no header
#21 von easyoradba am Dezember 12, 2016 - 16:10
Reblogged this on ..:::: EasyOraDBA | Shadab Mohammad ::::...
#22 von vizzi am Juni 21, 2018 - 10:45
hi..how can i add color to the table