How to display table data more clearly in oracle sqlplus

Question

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

User · Answer

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

User · Answer

Ahhh  the stupid linesize     Here is what I do in my profile sql - works only on unixes  echo SET LINES   tput cols   gt   HOME  login tmp sql   HOME  login tmp sql  if you find an equivalent for tput on Windows  it might work there as well

User · Answer

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

User · Answer

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

User · Answer

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

[database] How to display table data more clearly in oracle sqlplus

Examples related to database

Examples related to oracle

Examples related to sqlplus