René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Beautifying SQL PLUS Output

The output of SQL Plus can be a annoying a little bit. Here are some techniques that show how to enhance the readability of SQL Plus output
First, a table (beautify) is created and filled with some meaningless values, and then the query select a np, a ,b,c from beautify order by a, b is used on this table.
create table beautify (
   a  number(5),
   b  number(5),
   c  varchar2(200)
);

insert into beautify values (   1,   9,   'no way');
insert into beautify values (   3,   8,   'one way');
insert into beautify values (   3,   7,   'milky way');
insert into beautify values (   2,   6,   'way too much');
insert into beautify values (   1,   5,   'weigh');
insert into beautify values (   2,   4,   'why');
insert into beautify values (   1,   3,   'why not');
insert into beautify values (   3,   2,   'not');
insert into beautify values (   3,   1,   'not now');
insert into beautify values (   1,  10,   'now or never');
insert into beautify values (   2,  11,   'help');
insert into beautify values (   1,  12,   'no nelp');
insert into beautify values (   1,  10,   'float the boat, captain');
insert into beautify values (   3,   7,   'this is stupid');
insert into beautify values (   1,   5,   'hello');
If the query is issued now, the result will look like this (if nothing is was changed from the sql plus' default settings).
        NP          A          B
---------- ---------- ----------
C
-----------------------------------------------------------------------------------------------------------------------------
         1          1          3
why not


        NP          A          B
---------- ---------- ----------
C
-----------------------------------------------------------------------------------------------------------------------------
         1          1          5
weigh
This is mostly the case because the column c is to wide. We can easily make it smaller:
column c format a20
The output looks much better now:
        NP          A          B C
---------- ---------- ---------- --------------------
         1          1          3 why not
         1          1          5 weigh
         1          1          5 hello
         1          1          9 no way
         1          1         10 now or never
         1          1         10 float the boat, capt
                                 ain

         1          1         12 no nelp
         2          2          4 why
         2          2          6 way too much

        NP          A          B C
---------- ---------- ---------- --------------------
         2          2         11 help
         3          3          1 not now
         3          3          2 not
         3          3          7 milky way
         3          3          7 this is stupid
         3          3          8 one way
Yes! This looks much nicer. But let's get rid of these disturbing ------, also by setting the page size to a ridiculously, but effectiv, high value:
set pagesize 50000
        NP          A          B C
---------- ---------- ---------- --------------------
         1          1          3 why not
         1          1          5 weigh
         1          1          5 hello
         1          1          9 no way
         1          1         10 now or never
         1          1         10 float the boat, capt
                                 ain

         1          1         12 no nelp
         2          2          4 why
         2          2          6 way too much
         2          2         11 help
         3          3          1 not now
         3          3          2 not
         3          3          7 milky way
         3          3          7 this is stupid
         3          3          8 one way
Obviously, there is no need for the column np (in this example, this is on purpose). So, let's get rid of this column as well:
column np noprint
We are ordering by a and b. It would be nice if we could only print the first occurence of a specific value in a and print an empty line whenever the value of a changes:
break on a skip 1
Here's the output sofar:
         A          B C
---------- ---------- --------------------
         1          3 why not
                    5 weigh
                    5 hello
                    9 no way
                   10 now or never
                   10 float the boat, capt
                      ain

                   12 no nelp

         2          4 why
                    6 way too much
                   11 help

         3          1 not now
                    2 not
                    7 milky way
                    7 this is stupid
                    8 one way
Now, as a last feature: whenever a changes, let's print the sum of b for all rows that share the same a:
compute sum label 'sum of b' of b on a
         A          B C
---------- ---------- --------------------
         1          3 why not
                    5 weigh
                    5 hello
                    9 no way
                   10 now or never
                   10 float the boat, capt
                      ain

                   12 no nelp
********** ----------
sum of b           54

         2          4 why
                    6 way too much
                   11 help
********** ----------
sum of b           21

         3          1 not now
                    2 not
                    7 milky way
                    7 this is stupid
                    8 one way
********** ----------
sum of b           25