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

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

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

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

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

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   amp  amp   isDashed ln1   amp  amp   isDashed ln2     function isDataFirstLn   return isDashed ln2  amp  amp  isDashed ln1  amp  amp  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

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

Examples related to database

Examples related to oracle

Examples related to sqlplus