Sqlplus is my second home, Part 1: HTMLizing your sqlplus output

I have not managed to post anything for a while, but I intend to make it up by starting this series of posts made specially for Oracle enthusiasts, geeks and maniacs among us. Here I plan to post the coolest Oracle stuff I’ve just found out and some of it may actually be useful to you!

Lets start. This post is about removing the last major problem with sqlplus in everyday database and application administration work.

I would say the commnd line sqlplus, combined with its script execution and Windows cmd.exe’s command history navigation capabilities, is a very powerful and fast tool for database administration and troubleshooting. This is of course if you use a set of database administration scripts, either downloaded from some reliable source or accumulated over the years of working with Oracle ( you do have such scripts, right? ;-)

Now to the main weakness of sqlplus: I may have scripts carefully formatted for my screen size, however when adding more columns, I run out of screen width. When working on an application data quality troubleshooting task, I need to run some quick ad-hoc queries. Or run a query which just returns lots of data.

What usually happens in such cases is illustrated very well with the output of following query:

Tanel@Prod01> select * from v$session;

-------- ------- ------- ---------- -------- ---------- ------------ ---------- ---------- --------
-------- -------- --------- ---------- ------------------------------ ------------------------------
PROCESS      MACHINE                                                          TERMINAL
------------ ---------------------------------------------------------------- ----------------
PROGRAM                                                          TYPE       SQL_ADDR SQL_HASH_VALUE
---------------------------------------------------------------- ---------- -------- --------------
------------- ---------------- -------- --------------- ------------- -----------------
--------------------- ------------------------- --------------- -------------------
MODULE                                           MODULE_HASH ACTION
------------------------------------------------ ----------- --------------------------------
ACTION_HASH CLIENT_INFO                                                      FIXED_TABLE_SEQUENCE
----------- ---------------------------------------------------------------- --------------------
------------- -------------- --------------- ------------- ----------------- ------------ ---
------------- ---------- --- -------------------------------- -------- -------- --------
CURRENT_QUEUE_DURATION CLIENT_IDENTIFIER                                                BLOCKING_SE
---------------------- ---------------------------------------------------------------- -----------
----------------- ---------------- ---------- ----------
P1TEXT                                                                   P1 P1RAW
---------------------------------------------------------------- ---------- --------
P2TEXT                                                                   P2 P2RAW
---------------------------------------------------------------- ---------- --------
P3TEXT                                                                   P3 P3RAW    WAIT_CLASS_ID
---------------------------------------------------------------- ---------- -------- -------------
WAIT_CLASS# WAIT_CLASS                                                        WAIT_TIME
----------- ---------------------------------------------------------------- ----------
--------------- ------------------- ----------------------------------------------------------------
-------- ----- -----
3433AA6C     151       1          0 3425173C          0                       0 2147483644
         ACTIVE   DEDICATED          0 SYS                            SYSTEM
69912        WINDOWS01                                                        WINDOWS01
ORACLE.EXE (q001)                                                BACKGROUND 33E70384     3393152264
4gd6b1r53yt88                0 00                     0                               0

          0                                                                                    24
           -1              0               0             0 20070807 21:45:53          255 NO
NONE          NONE       NO                                   DISABLED ENABLED  ENABLED
                     0                                                                  NO HOLDER
                                            2        268
Streams AQ: waiting for time management or cleanup tasks
                                                                          0 00
                                                                          0 00
                                                                          0 00          2723168908
          6 Idle                                                                      0
            255 WAITING             SYS$BACKGROUND

3433BD54     152       1          0 3425114C          0                       0 2147483644
         ACTIVE   DEDICATED          0 SYS                            SYSTEM
73008        WINDOWS01                                                        WINDOWS01
ORACLE.EXE (q000)                                                BACKGROUND 00                    0

Yes – the output is unreadable junk.

So, what do we normally do in such cases:

A) we switch to GUI client tools – these have their own limitations (like not having a decent sqlplus script execution facility, GUIs not being available on all machines, etc)

B) we make our sqlplus window and linesize waaay larger – this also means that we have to start doing a lot of horizontal scrolling to get past the varchar2(1000) columns if we want to see the “right side” of the table

C) we put our queries into well-formatted prepared sqlplus scripts – yes, that’s what I do for frequently used tasks, but these scripts are usually pretty static and still suffer from lack of formatting should we need a very “wide” output

So I present the option D – sqlplus HTMLizer !

As many new things, it’s really nothing fundamentally new. It uses sqlplus HTML markup capabilities which have been available since forever (Oracle8i I think).
But how easily it can be used is sure cool and useful! Check this out:

[...some junk output from last query eliminated...]
343511A4     170       1          0 3424B24C          0                       0 2147483644
         ACTIVE   DEDICATED          0 SYS                            SYSTEM
3012         WINDOWS01                                                        WINDOWS01
ORACLE.EXE (PMON)                                                BACKGROUND 00
                             0 00                     0                               0

          0                                                                                     0
           -1              0               0             0 20070807 21:45:30          277 NO
NONE          NONE       NO                                   DISABLED DISABLED DISABLED
                     0                                                                  NO HOLDER
                                            3          1
pmon timer
duration                                                                300 0000012C
                                                                          0 00
                                                                          0 00          2723168908
          6 Idle                                                                      0
            264 WAITING             SYS$BACKGROUND

15 rows selected.

Tanel@Prod01> @html 

 Query output in HTML format

The html script will rerun the query in your sqlplus buffer, spool its output to file and display it in your default web browser. You like it? :-)

Also there’s another script which takes the query to be executed as a parameter (between double-quotes):

Tanel@Prod01> @htmlrun "select * from dba_objects where rownum<=10" 

Output of a SQL statment passed to htmlrun.sql as parameter

Want the output into Excel?

Tanel@Prod01> set linesize 32000
Tanel@Prod01> select * from all_objects where rownum <=10;

OWNER                          OBJECT_NAME                    SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED
------------------------------ ------------------------------ ------------------------------ ---------- -------------- ------------------- -----------------
 ----------------- ------------------- ------- - - -
SYS                            ICOL$                                                                 20              2 TABLE               20070109 22:19:33
 20070109 22:45:39 2007-01-09:22:19:33 VALID   N N N
SYS                            I_USER1                                                               44             44 INDEX               20070109 22:19:33
 20070109 22:19:33 2007-01-09:22:19:33 VALID   N N N
SYS                            CON$                                                                  28             28 TABLE               20070109 22:19:33
 20070109 22:45:39 2007-01-09:22:19:33 VALID   N N N
SYS                            UNDO$                                                                 15             15 TABLE               20070109 22:19:33
 20070109 22:19:33 2007-01-09:22:19:33 VALID   N N N
SYS                            C_COBJ#                                                               29             29 CLUSTER             20070109 22:19:33
 20070109 22:19:33 2007-01-09:22:19:33 VALID   N N N
SYS                            I_OBJ#                                                                 3              3 INDEX               20070109 22:19:33
 20070109 22:19:33 2007-01-09:22:19:33 VALID   N N N
SYS                            PROXY_ROLE_DATA$                                                      25             25 TABLE               20070109 22:19:33
 20070109 22:19:33 2007-01-09:22:19:33 VALID   N N N
SYS                            I_IND1                                                                39             39 INDEX               20070109 22:19:33
 20070109 22:19:33 2007-01-09:22:19:33 VALID   N N N
SYS                            I_CDEF2                                                               51             51 INDEX               20070109 22:19:33
 20070109 22:19:33 2007-01-09:22:19:33 VALID   N N N
SYS                            I_PROXY_ROLE_DATA$_1                                                  26             26 INDEX               20070109 22:19:33
 20070109 22:19:33 2007-01-09:22:19:33 VALID   N N N

10 rows selected.

Tanel@Prod01> @csv
 Query output saved into .csv file and opened in Excel

If you like this, you can download the scripts from my scripts directory

If you're wondering how this works, here's the html.sql in text form:

set termout off

set markup HTML ON HEAD " -
  body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} -
  p {   font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} -
        table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; -
        padding:0px 0px 0px 0px; margin:0px 0px 0px 0px; white-space:nowrap;} -
  th {  font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; -
        padding:0px 0px 0px 0px;} -
  h1 {  font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; -
        border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} -
  h2 {  font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; -
        margin-top:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#663300; -
        background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
" -
BODY "" -
TABLE "border='1' align='center' summary='Script output'" -

spool %SQLPATH%tmpoutput_&_connect_identifier..html


spool off
set markup html off spool off
host start %SQLPATH%tmpoutput_&_connect_identifier..html
set termout on

Note that for flexibility I am spooling to a %SQLPATH%\tmp directory and am using the &_connect_identifier sqlplus variable (available from sqlplus 10g). You may want to change those to point to a static location (like c:\temp or /tmp) , especially if you plan to use such scripts in a number of different environments.

 A warning note: as both the html.sql and csv.sql rerun the query in your sqlplus buffer for spooling the output, use these scripts with slight caution - e.g. don't try to html'ize the output of "truncate table X" command unless you really want to get rid of that table's contents again!

 Happy htmlizing!

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

This entry was posted in Cool stuff and tagged . Bookmark the permalink.

63 Responses to Sqlplus is my second home, Part 1: HTMLizing your sqlplus output

  1. SQL*Plus, I already liked it for years and years. Thats why I will miss the sqlplusw.exe variant. I never really like the cmd environment. I even maybe will install cgywin to circumvent this problem regarding sqlplus 11g on windows. Despite this is the first time I realized that the markup functionality. It is horrible to see nowaydays, that you can impress someone on the otn forums with simple stuff like “ttitle” or comp statements. Sometimes it is no wonder people have something against the “plus” in “sqlplus”; if they would have the manual (if it is still in there) wouldn’t be so disappointed – it can do more than they realize…

  2. ehhh, correction…

    “Despite this it is the first time that I realized that the markup functionality can be useful.”

  3. chris says:

    Thanks a lot for these scripts! As I mostly work with vi and sql*plus, they will be really useful. Your csv/dsv-scripts don’t work for me, though. Excel puts all the data in the first column (I also tried with “;” as separator). What does work is simply spooling the html output to a .xls file.

  4. tanelp says:

    Thanks Marco and Chris for the comments.

    Chris, which version of Excel are you using? Try this manually:

    1) copy & paste some csv content to Excel (leave all text to first column)
    2) select that first column and go to Data -> Text to columns in Excel menu
    3) Select comma as delimiter and format text to columns
    4) Close Excel
    5) Try the @csv script again

    Btw, this saving of html to .xls file is a cool idea. It gives very nicely formatted output for me. I think I’ll create an @xls script for that :)

  5. Sidhu says:

    Good stuff Tanel

    Chris, What you are looking for, I just wrote about that on my blog, spooling to xls file from SQL*Plus



  6. Jared says:

    Playing with the csv.sql, I added the following to the top of the file:

    clear columns
    clear breaks
    set linesize 5000
    set pagesize 50000

    The clear command are self explanatory.

    I set linesize to 5000 as the script can’t deal with lines that wrap. Adjust if needed, I forget the max value for this.

    pagesize was set to 50000 so one line of column heading would appear as the first line in the CSV file.

    If you have more than 50,000 lines, you will see another column heading line.

    If you have more than 65535 lines, you can’t do it in SQLPlus, as that is the max number of lines in an Excel worksheet.

  7. tanelp says:

    Thanks Jared!

    Yep I was lazy putting all the formatting settings into the csv script as I’m using a standard init.sql (which is automatically called by %SQLPATH%\login.sql) and everything is set there, globally.

    This is an example how to get comma separated and “-quoted output in sqlplus (but as you said, it’s probably not exactly what’s needed as this adds two dummy columns and pads values with spaces or tabs):

    SQL> set colsep ‘”,”‘
    SQL> select ‘”‘ prefix,s.*,’”‘ suffix from v$mystat s where rownum select
    2 regexp_replace(
    3 regexp_replace(to_char(sid), ‘(^)(.*)($)’, ‘”\2″,’) ||
    4 regexp_replace(to_char(statistic#), ‘(^)(.*)($)’, ‘”\2″,’) ||
    5 regexp_replace(to_char(value), ‘(^)(.*)($)’, ‘”\2″,’)
    6 , ‘,$’, ”) output
    7 from
    8 v$mystat
    9 where
    10 rownum <= 10
    11 /


    10 rows selected.

    This approach requires you to manually specify the columns you’re selecting from.

    Btw, if you want to see really flexible solutions for automatically dealing with varying table structures, check this:

  8. tanelp says:

    Btw, the Excel 65536 row limit should have been lifted in Excel 2000…

  9. Jared says:

    Add to my previous comment:

    set trimspool on

  10. Jared says:

    Even more fun…

    I attempted to get dsv to output data separated by double quotes and commas.

    eg. “this”,”is”,”a”,”test”

    You can get 99% of the way with the following:

    In dsv.sql, put single quotes around ‘&1′.

    Call dsv.sql like this:

    @dsv ‘”,”‘

    The only thing missing in the output are the leading and trailing quotes.

    Modifying the query came to mind:

    select * from user_tables

    select ‘”‘, u.*, ‘”‘ from user_tables u

    That doesn’t quite work either.

    But if you can live with just comma separated output, this works great.

    Thanks Tanel.

  11. Jared says:

    Thanks for the link to Method 4 Dynamic SQL. That is really an awesome bit of code.

  12. tanelp says:

    Jared, it turns out that the 65536 row limitation is still there up to Excel 2003 :(

    Excel 2000 lifted the limitation from 16k to 65k, not to 1 million as I remembered. However I’ve read that the Excel 2007 and server side Excel components now have lifted the rowcount limitation to 1M.

  13. Jared says:

    Yes, I knew the 65536 row limitation was still there.

    About 3 years ago I wrote some scripts (apps really) to create Excel files from Perl. These are for SOX reporting, and spit out up to 200k lines.

    Seemed kind of silly that the line counter in Excel was only 8 bits long.

    It also complicate the programming somewhat.

    BTW, this was using the Spreadsheet::WriteExcel Perl module, which outputs Excel files, not CSV. Very nice module.

  14. Pingback: Hotsos Symposium 2008 Day Two « Seven Seconds

  15. A says:


    Here is something very easy and useful:

    Create a file X.sql with:
    select * from hr;

    Then call it from the commandline using:
    SQLPLUS -r 3 -L -S -M “HTML ON ENTMAP ON TABLE ‘BORDER=”2″‘” UName/PW@tns @X.sql > report.html

    It will be pretty (if you like OLAF) and work in XL just fine.

  16. Mike says:

    Excel 2007 does 1 million rows, and 16000 columns.

  17. vv says:

    Great Stuff Tanel, Awesome ..
    Just a small Question .. When I use these on a SQL*Plus session invoked on Zsh, I get the following note:
    ‘fg: no job control in this shell.’
    I guess this is because I am using environment variables %SQLPATH, %tmpout, etc for output which are not defined. If I hard code a location for output, then I get permission denied. Is there a way I can get rid of these warnings. I am getting correct output always, so its not a great concern. Reply as you have time.

    Thanks so much .. vv

  18. tanelp says:

    Hi vv,

    I wrote this script for Windows OS and it needs a little customization for Unixes. I’ll do it some day and blog it.

  19. Kevin Grant says:

    Great scripts Tanel :)

    Here are my edits to get them working on my MacBook Pro running OSX 10.5.3:

    Replaced (in html.sql & htmlrun.sql):
    spool %SQLPATH%tmpoutput_&_connect_identifier..html
    spool /tmp/output_&_connect_identifier..html

    host start %SQLPATH%tmpoutput_&_connect_identifier..html
    host open /tmp/output_&_connect_identifier..html

    This may also work on other Unix based OS’s.

  20. tanelp says:

    Thanks Kevin, yep that’s pretty much what’s needed to use this on Unix. I didn’t know about the “open” command though and just used firefox directly (open may be just OSX command though…)

  21. Kevin Grant says:

    Had a look at the man page and it looks like ‘open’ is Mac specific. It first appeared in NextStep apparently ;)

    It’s useful in this case because it will open the ‘html’ file on your default browser (Safari/Firefox/Camino/whatever). There’s no need to find the executable etc. etc.

  22. Paul Gilet says:

    Thanks for the scripts. Whilst they solved part of my problem, they are a little slow but I’m sure that I will get a lot of use from them.
    After 10 years of battling with windows,I still miss UNIX. (I started on Oracle v4 in 1986)
    My problem is the sqlplus screen output is being truncated at about 100 characters (not counting spaces) regardless of the LINESIZE setting. Any solutions.

    Thanks again


  23. tanelp says:

    The html script works quite fast for me. Of course sqlplus has to fetch the data from remote side, so over slower connections you the wait for data fetching can be long. Setting arraysize larger will help if the connection has high response latency.

    Where do you experience this truncation problem? is it Windows SQLPLUS (the GUI version?). i think that one had some issues like that. But i have never used it, I use sqlplus.exe (instead of sqlplusW.exe) which is a command line version and much more convenient to use (command line history, navigation and try the F7 key for example)

  24. anantha says:

    how do i execute a sql script using htmlrun.i have a sql script which has hundreds of select statements.

    how do i achieve the same?

  25. tanelp says:

    Just look into the script… Instead of running the last command in buffer you need to call your script after the HTML mode is enabled.

    Shortly, the steps are here:


    2) SPOOL yourfile.html

    3) @your_script.sql


    5) SPOOL OFF

  26. Andri says:

    How to do it in mutiple command in one script?

    is it possible?

  27. Tanel Poder says:

    Hi Andri,

    Well you can do something like that:


    2) SPOOL yourfile.html

    3) @your_script_1.sql


    5) SPOOL OFF

    So, just SET MARKUP HTML ON SPOOL ON and run whatever you want after that…

  28. Andri says:

    how its combine with your html.sql?

  29. Andri says:

    Tanel Poder Sir,

    Its working, i figure it out my self,

  30. Rafaele says:


    that is a great script. i use this on a daily basis. thank you for posting it. really simple and effective.


  31. NorbertK says:

    htmlrun really rocks !

  32. rock2 says:

    This script ROCKS…i love it…but quick q…how do i run snapper inside this script ?? If i wanted the results out of snapper.sql into this html file format ??

    do i do @htmlrun “@snapper.sql 1 1 sid”


  33. Tanel Poder says:

    Yep I also think so :)

    Snapper doesnt output in tabular format, I just just dbms_output to print out lines so there would be little value trying to HTML’ize that output. I have a script called hs.sql (HTML script run) which basically enables HTML, runs the script and runs the output file. It’s in the TPT_public.zip file…

  34. rock2 says:

    Thanks Tanel, that works like a charm…@htmlrun “@snapper.sql out 1 1 sid” …the reason why i wanted to do this is becoz..working on this sql which takes about 2 hours to get results and management wanted to see what i can do…originally it took over 7 hours to run…i brought it down to 2 hours..(lol) it query 35 different tables and also not to mention using a 3 funtions in the select statment….and now i go use ur snapper tool and htmlzier tool and give them a html report on what exactly that sql is waiting on and stuff like that…


    YOU ROCK ……

  35. pandi says:

    iam calling a sql file from unix shell script to generate in html. Find below the script in unix

    title=”Report- Rate Sheet Length12″

    sqlplus -S ${oraUser}@${oraSID}/${oraPWD} @${oraSQLFile} ${oraOutFile} ${oraDB} ${oraView} ${title}
    in the above script, i pass the title as fourth parameter to print as title for the report.

    sql file content:set newpage 1
    set space 5
    set linesize 4500
    set pagesize 51
    set echo off
    set feedback on
    set heading on
    set markup html on
    set term off
    set trims on
    set verify off
    spool &1
    TTITLE &4
    select * from
    &2..&3 ;

    The report output will be displayed as Report- instead of Rate Sheet Length12. it will skip the remaining part once the space is entered.

  36. pandi says:

    Please suggest a solution for the above.

  37. Tanel Poder says:

    Use “${title}” instead of ${title} at unix prompt

  38. pandi says:

    Thanks tanel.if i give “${title}” instead of ${title} at unix prompt.
    the title is displayed but there is no spaces between words

    it is like
    Report-RateSheetLength12 instead of Report- Rate Sheet Length12.is there any other option to overcome this. one more question.

    i have
    set heading on in the sql file.

    column heading is truncates to the length of the underlying data type.is there any option to overcome this. give some sample sql file.

    i have

  39. fabio says:

    thanks Tanel. This script was incredible!! You save my working time!

  40. Pingback: Variabili in SQL*Plus « Oracle and other

  41. Vikas Bagga says:

    Hi Tanel
    I try running the html.sql with a slight change in UNIX (AIX)
    ! uuencode -m /tmp/output_&_connect_identifier..html /tmp/output_&_connect_identifier..html | mailx -s”TEST: ${ORACLE_SID}@`hostname`” vbagga@secureworks.com </tmp/o
    In outlook I get the entire HTML dump wrapper including the result set. I can copy this to a notepad and open it in the html format.Can you advise me on whether I need to change the setting of my mailx option or my outlook options?

  42. Tanel Poder says:

    Hi Vikas,

    Are you trying to have the HTML as an attachment in your mail?

  43. Vikas Bagga says:

    no. In the body.

  44. Tanel Poder says:

    I have done only the attachment thing using mailx (via adding some MIME headers).

    Basically you need to create a multipart message (by modifying mail headers) and the HTML code should be in the text/html part of that message.

    You can take any other HTML email and see it in its raw form and you basically need to produce the same using mailx. However mailx (unless you have the -m option) adds some of its own headers already so you can’t force a multipart message easily.

  45. Tanel Poder says:

    unfortunately i don’t have time to test this myself, but check this for an example of how a multipart message would look like:


    then you need to add your HTML part in the text/html type sub-part..

  46. ben says:

    Hi. may i seek your expert advice on how to export a sql query to excel2003 to multiple worksheets if the data exceed 64k records ?

  47. Mark says:

    How do I add a scrollbar to a table ?
    I want to run a script (containing many sqls) and the output should be on single page. SO I need to have a scroll bar in each table.
    Is it possible and how ?

  48. Laxman says:

    God Bless You!

  49. david says:

    Thanks for the post –
    I like sqlplus HTML output for simplicity –
    however missing one thing – alternating color for every other row,
    for clarity while viewing wide reports.

    is there any way to embed HTML color for every row, like make it dependent on mod(rownum/2) – even/odd row number ?

  50. Tanel Poder says:

    I don’t know of a way with just sqlplus and its MARKUP tag (although you can change CSS in the MARKUP tag)

    One option would be to modify the html script to post-process the spooled file – run some SED, AWK or VBScript command on the output html file which would just add different style to every other TR tag before opening the file…

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>