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
` ``
`
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="https://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
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 offset 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!
