SQL*Plus output in nice HTML format

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:

http://blog.tanelpoder.com/2007/08/07/sqlplus-is-my-second-home-part-1-htmlizing-your-sqlplus-output/

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?

About these ads
  1. #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. #2 by Walter on June 30, 2011 - 23:48

    Forgot to mention, the above command is if you are on windows.

    host start… ..

  3. #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. #4 by Laurent Schneider on July 1, 2011 - 11:16

    Any change to get valid html?

    prom hello world

    hello 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. #5 by Laurent Schneider on July 1, 2011 - 11:17

    prom hello world
    <br>
    hello world
    <br>

  6. #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. #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. #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. #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. #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. #11 by Uwe Hesse on July 1, 2011 - 20:26

    Got your point now, Laurent :-)

  12. #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. #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. #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. #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. #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. #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. #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. #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. #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

  1. Sqlplus output | Tamihughson

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,708 other followers

%d bloggers like this: