Martins Blog

Trying to explain complex things in simple terms

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):

nice output

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):

not so nice output

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.

Solutions

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 :)

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 2,492 other followers

%d bloggers like this: