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

Tanel Poder

2007/08/07

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 

 <img border="0" width="800" src="http://tanelpoder.files.wordpress.com/2007/08/htmlizer.png?resize=800%2C524" alt="Query output in HTML format" height="524" data-recalc-dims="1" /></p> <pre></pre> <p>

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" 

<img border="0" width="800" src="http://tanelpoder.files.wordpress.com/2007/08/htmlizer2.png?resize=800%2C457" alt="Output of a SQL statment passed to htmlrun.sql as parameter" height="457" data-recalc-dims="1" /><br />  <br />

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!


NB! Check out my 2018/2019 online training classes: Advanced Oracle SQL Tuning training, Advanced Oracle Troubleshooting training and the new Practical Linux Performance & Application Troubleshooting training!