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

  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,430 other followers

%d bloggers like this: