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

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:

enter image description here

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

This question is related to database oracle sqlplus

The answer is


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.


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

A little preview of parsed and non-parsed output


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;

Ahhh, the stupid linesize ... Here is what I do in my profile.sql - works only on unixes:

echo SET LINES $(tput cols) > $HOME/.login_tmp.sql
@$HOME/.login_tmp.sql

if you find an equivalent for tput on Windows, it might work there as well


Examples related to database

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to sqlplus

When or Why to use a "SET DEFINE OFF" in Oracle Database SQLPLUS error:ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory How do I resolve this "ORA-01109: database not open" error? How to echo text during SQL script execution in SQLPLUS PL/SQL ORA-01422: exact fetch returns more than requested number of rows how to pass variable from shell script to sqlplus Connect to Oracle DB using sqlplus sqlplus how to find details of the currently connected database session How to output oracle sql result into a file in windows?