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;

SADDR        SID SERIAL#     AUDSID PADDR         USER# USERNAME        COMMAND    OWNERID TADDR
-------- ------- ------- ---------- -------- ---------- ------------ ---------- ---------- --------
LOCKWAIT STATUS   SERVER       SCHEMA# SCHEMANAME                     OSUSER
-------- -------- --------- ---------- ------------------------------ ------------------------------
PROCESS      MACHINE                                                          TERMINAL
------------ ---------------------------------------------------------------- ----------------
PROGRAM                                                          TYPE       SQL_ADDR SQL_HASH_VALUE
---------------------------------------------------------------- ---------- -------- --------------
SQL_ID        SQL_CHILD_NUMBER PREV_SQL PREV_HASH_VALUE PREV_SQL_ID   PREV_CHILD_NUMBER
------------- ---------------- -------- --------------- ------------- -----------------
PLSQL_ENTRY_OBJECT_ID PLSQL_ENTRY_SUBPROGRAM_ID PLSQL_OBJECT_ID PLSQL_SUBPROGRAM_ID
--------------------- ------------------------- --------------- -------------------
MODULE                                           MODULE_HASH ACTION
------------------------------------------------ ----------- --------------------------------
ACTION_HASH CLIENT_INFO                                                      FIXED_TABLE_SEQUENCE
----------- ---------------------------------------------------------------- --------------------
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIME        LAST_CALL_ET PDM
------------- -------------- --------------- ------------- ----------------- ------------ ---
FAILOVER_TYPE FAILOVER_M FAI RESOURCE_CONSUMER_GROUP          PDML_STA PDDL_STA PQ_STATU
------------- ---------- --- -------------------------------- -------- -------- --------
CURRENT_QUEUE_DURATION CLIENT_IDENTIFIER                                                BLOCKING_SE
---------------------- ---------------------------------------------------------------- -----------
BLOCKING_INSTANCE BLOCKING_SESSION       SEQ#     EVENT#
----------------- ---------------- ---------- ----------
EVENT
----------------------------------------------------------------
P1TEXT                                                                   P1 P1RAW
---------------------------------------------------------------- ---------- --------
P2TEXT                                                                   P2 P2RAW
---------------------------------------------------------------- ---------- --------
P3TEXT                                                                   P3 P3RAW    WAIT_CLASS_ID
---------------------------------------------------------------- ---------- -------- -------------
WAIT_CLASS# WAIT_CLASS                                                        WAIT_TIME
----------- ---------------------------------------------------------------- ----------
SECONDS_IN_WAIT STATE               SERVICE_NAME
--------------- ------------------- ----------------------------------------------------------------
SQL_TRAC SQL_T SQL_T
-------- ----- -----
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
          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
DISABLED FALSE FALSE

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                                                                                     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
DISABLED FALSE FALSE

15 rows selected.

Tanel@Prod01>
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
 LAST_DDL_TIME     TIMESTAMP           STATUS  T G S
------------------------------ ------------------------------ ------------------------------ ---------- -------------- ------------------- -----------------
 ----------------- ------------------- ------- - - -
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 ON ENTMAP ON PREFORMAT OFF

spool %SQLPATH%tmpoutput_&_connect_identifier..html

l
/

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. abc says:

    Tanel!

    It’s just brilliant! Thanks!

    Terminal junkies like me may replace a gui browser with elinks or links (they can render tables).

    David:
    if You are using firefox or chrome browser then it’s possible to use an “:nth-child()” pseudo-class.

    1) split “table,tr,td%” line into two and remove background from tr and td elements:

    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;} -
    tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; -
    padding:0px 0px 0px 0px; margin:0px 0px 0px 0px; white-space:nowrap;} -

    2) add two lines “nth-child()”

    tr:nth-child(even) {background: #ddddee} -
    tr:nth-child(odd) {background: #ffeeee} –

    NB! IE7 will not work.

  2. WJH says:

    Hi,

    I just tried to download the zip file sqlplus_htmlizer.zip and it gave the error, will you kindly check?

    Thanks.
    ___________________________________________________________________________________

    Welcome to the 404 error page!

    Welcome to this customized error page. You’ve reached this page because you’ve clicked on a link that does not exist. This is probably our fault… but instead of showing you the basic ’404 Error’ page that is confusing and doesn’t really explain anything, we’ve created this page to explain what went wrong.

    You can either (a) click on the ‘back’ button in your browser and try to navigate through our site in a different direction, or (b) click on the following link to go to homepage.

  3. Sherry says:

    Hi Tanel,

    I, too, have just tried to download the sqlplus_htmlizer.zip file and received the customized 404 error. I noticed on your blog home, that you provided a link to a page where some scripts could be downloaded, however, I did not see this script on that site. Is there another way to download it? Thanks so much for sharing your knowledge.

    Sherry

  4. Sherry says:

    Hi Tanel,

    Please disregard my previous post. I found the html.sql and csv.sql scripts in the zip file from the link on your blog home. At first glance I thought the scripts in that zip file were for troubleshooting only. Then, I downloaded it just to see what was in it, and found the scripts I was looking for. Thanks again!

    Sherry

  5. Pingback: SQL*Plus output in nice HTML format « The Oracle Instructor

  6. Rich says:

    For those who are having trouble finding the zip file with the scripts in, you can use the following post has details:

    http://blog.tanelpoder.com/2011/04/15/new-scripts-tools-and-broken-links/

  7. Jon says:

    Hi,
    I tried running the script, i got this error, “/bin/ksh: start: not found”.
    OS info:

    $ uname -a
    SunOS crater 5.8 Generic_108528-29 sun4u sparc SUNW,Sun-Fire-280R

    Please assist.

    Thanks.

  8. ptrck says:

    I have followed the above links and i can not find the link to download the sqlplus_htmlizer.zip.. can anyone post a direct link if they know where this dl is? Thanks!

  9. Pingback: sql_sql_id_html.sql: a SQL performance script with HTML output | Marcel-Jan's Oracle Blog

  10. Azhar Syed says:

    Tanel ,

    Thanks for the great article . Any inputs how to avoid the actual query in the html report. Just want the output of the query displayed in the report.

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>