How to display table data more clearly in oracle sqlplus
Solution 1
I usually start with something like:
set lines 256
set trimout on
set tab off
Have a look at help set
if you have the help information installed. And then select name,address
rather than select *
if you really only want those two columns.
Solution 2
If you mean you want to see them like this:
WORKPLACEID NAME ADDRESS TELEPHONE
----------- ---------- -------------- ---------
1 HSBC Nugegoda Road 43434
2 HNB Bank Colombo Road 223423
then in SQL Plus you can set the column widths like this (for example):
column name format a10
column address format a20
column telephone format 999999999
You can also specify the line size and page size if necessary like this:
set linesize 100 pagesize 50
You do this by typing those commands into SQL Plus before running the query. Or you can put these commands and the query into a script file e.g. myscript.sql and run that. For example:
column name format a10
column address format a20
column telephone format 999999999
select name, address, telephone
from mytable;
Solution 3
You can set the line size as per the width of the window and set wrap off using the following command.
set linesize 160;
set wrap off;
I have used 160 as per my preference you can set it to somewhere between 100 - 200
and setting wrap will not your data and it will display the data properly.
Solution 4
In case you have a dump made with sqlplus and the output is garbled as someone did not set those 3 values before, there's a way out.
Just a couple hours ago DB admin send me that ugly looking output of query executed in sqlplus (I dunno, maybe he hates me...). I had to find a way out: this is an awk script to parse that output to make it at least more readable. It's far not perfect, but I did not have enough time to polish it properly. Anyway, it does the job quite well.
awk ' function isDashed(ln){return ln ~ /^---+/};function addLn(){ln2=ln1; ln1=ln0;ln0=$0};function isLoaded(){return l==1||ln2!=""}; function printHeader(){hdr=hnames"\n"hdash;if(hdr!=lastHeader){lastHeader=hdr;print hdr};hnames="";hdash=""};function isHeaderFirstLn(){return isDashed(ln0) && !isDashed(ln1) && !isDashed(ln2) }; function isDataFirstLn(){return isDashed(ln2)&&!isDashed(ln1)&&!isDashed(ln0)} BEGIN{_d=1;h=1;hnames="";hdash="";val="";ln2="";ln1="";ln0="";fheadln=""} { addLn(); if(!isLoaded()){next}; l=1; if(h==1){if(!isDataFirstLn()){if(_d==0){hnames=hnames" "ln1;_d=1;}else{hdash=hdash" "ln1;_d=0}}else{_d=0;h=0;val=ln1;printHeader()}}else{if(!isHeaderFirstLn()){val=val" "ln1}else{print val;val="";_d=1;h=1;hnames=ln1}} }END{if(val!="")print val}'
In case anyone else would like to try improve this script, below are the variables: hnames -- column names in the header, hdash - dashed below the header, h -- whether I'm currently parsing header (then ==1), val -- the data, _d - - to swap between hnames and hdash, ln0 - last line read, ln1 - line read previously (it's the one i'm actually working with), ln2 - line read before ln1
Happy parsing!
Oh, almost forgot... I use this to prettify sqlplus output myself:
[oracle@ora ~]$ cat prettify_sql
set lines 256
set trimout on
set tab off
set pagesize 100
set colsep " | "
colsep is optional, but it makes output look like sqlite which is easier to parse using scripts.
EDIT: A little preview of parsed and non-parsed output
Related videos on Youtube
Nubkadiya
Updated on February 27, 2021Comments
-
Nubkadiya about 3 years
I want to be able to display the resulting data from a select in a pretty way, not all columns under others.
Here is the way sqlplus displays my table data:
But I want to show them as:
Name | Address | Phone | -------+---------------+-------------+ name1 | address1 | phone1 | name2 | address2 | phone2 | name3 | address3 | phone3 |
Not each column under the other
-
Nubkadiya almost 14 yearserrr.. i want to show them just like a normal table. one after another one
-
-
APC almost 14 yearsAlso we would probably want to
SET PAGESIZE 200
(say) in order to reduce the repetition of the column headings in a big resultset. -
Nubkadiya almost 14 yearsso this should done during the table creation or can you please elobarate more on this
-
Alex Poole over 9 years@user206168 - the question was about SQL*Plus. Can you expand on what doesn't work?
-
Caffeinated over 9 yearsWhat's the point of the
set space 1
and theset tab off
? -
Alex Poole over 9 years@Coffee - I set tab off so it pads things with spaces instead of tab characters; doesn't matter on-screen but if the output is put in a file then it can have odd effects, if tab widths aren't the same.
set space 1
is redundant though;space
is obsolete for start, and it defaults to 1 anyway; but it's the equivalent ofset colsep ' '
. I think I've carried this from a really old environment that defaulted to three spaces between columns, and I just preferred one. -
Felipe Alvarez over 7 years
lines
andpages
NEVER worked for me. Usingcolumn
is easy, and fantastic. Thanks