| René Nyffenegger's collection of things on the web | |
|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
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
|