SQLPlus and white space in the output
Posted by Martin Bach on December 16, 2013
I finally managed to work around My Most Annoying Problem (TM) with SQLPlus in my career. SQLPlus will mess up my output when I am copying/posting something in wordpress or elsewhere. Here’s an example to show you what I mean. The output in SQLPlus in my terminal window is all nice and pretty (it’s a print screen):
Many thanks to Jonathan Lewis and his presentation about generating test data by the way!
The above output looks nice at first glance, so I’m copying and pasting this into a document, only to see this (again a print screen; this time from TextWrangler):
Obviously that isn’t great-why is the layout broken there?
What is not immediately visible is that SQLPlus uses TAB characters where in my opinion should use white spaces instead. This can be a problem for me but it is not necessarily one for you. Depending on your tab width in your favourite editor everything can be perfect if your tab-width matches the one used in SQLPlus. If it doesn’t you get the result as shown above.
So far I have use the old spool /path/to/spoolfile approach to spool data into a text file and then transferred it to my system. But today I got so annoyed with this that I actually looked into the documentation. And fair enough, I found the reference to this behaviour using the SET TAB option:
[set tab, ed.] Determines how SQL*Plus formats white space in terminal output. OFF uses spaces to format white space in the output. ON uses the TAB character. TAB settings are every eight characters. The default value for TAB is system dependent.
So that’s it then! I don’t like the tabs, they are unpredictable. White spaces are predictable so let’s see what happens:
SQL> show tab tab ON SQL> set tab off SQL> select /*+ gather_plan_statistics monitor */ /* martintest006 */ * from 2 clustered_lrg where trunc(date_created) = 3* to_date('2013-08-08','yyyy-mm-dd') and state = 'RARE'; ID T_PAD DATE_CREA DATE_COMP STATE --------------- ------------------------------ --------- --------- ------------ 147999 14800000 08-AUG-13 08-AUG-13 RARE 149999 15000000 08-AUG-13 09-AUG-13 RARE 148999 14900000 08-AUG-13 08-AUG-13 RARE 3 rows selected.
As you can see everything is aligned perfectly! The same is true for the TextWrangler output although I’m not uploading the screenshot to prove. I hope that this post saves some technical editors some nerves :)