Ugly formatting in SQL*Plus

Question

It is really annoying that when I run a select command in SQL*Plus such as:

SELECT * FROM books;

The output is really badly formatted and unreadable (row cells are not in a row but separated by line breaks etc):

enter image description here

How can I configure it to show SELECT results in a nicer way?

EDIT:

This is my login.sql file contents:

SET ECHO OFF
SET SERVEROUTPUT ON SIZE 1000000
SET PAGESIZE 999
SET LINESIZE 132

EDIT2:

Affer increasing the LINESIZE:

SET LINESIZE 32000

It now looks like this:

enter image description here

You might be interested in http://stackoverflow.com/questions/2228680/displaying-results-vertically-in-oracle. — René Nyffenegger, Dec 08 '11 at 06:37
Lots of overlap/duplication with http://stackoverflow.com/questions/5771573 — Trenton, Jul 31 '15 at 16:49

score 34 · Accepted Answer · answered Apr 24 '11 at 16:06

34

Increase the linesize, e.g SET LINESIZE 32000

or use SET WRAP OFF (but this will truncate long values)

answered Apr 24 '11 at 16:06

1

@a_horse_with_no_name Ok this helped a bit but it still looks horrific. Look at the second image I added to my question. – Richard Knop Apr 24 '11 at 16:12
@Richard: did you try `SET WRAP OFF` – Apr 24 '11 at 16:13
@a_horse_with_no_name Yes that made it nicer but it only shows first two columns now! – Richard Knop Apr 24 '11 at 16:20
2

@Richard Knop: I think it's high time you defined the `nicer way` bit of your question more precisely. :) – Andriy M Apr 24 '11 at 16:31
@Richard Knop: as long as you don't increase the width of your terminal I don't think SQL*Plus will ever make your console window showing a horizontal scrollbar. But why don't you use a GUI tool that can makes dealing with these kind of tables a lot easier? – Apr 24 '11 at 16:42
@a_horse_with_no_name Well, I'm just learning and following a book where SQL*Plus is used. But don't worry, I have figured a way to make it look a little bit more readable. It's not perfect but oh well. – Richard Knop Apr 24 '11 at 16:54
3

If you want to make it permanent (i.e. every time you log into SQL*Plus), add those lines to *glogin.sql* in **$ORACLE_HOME/sqlplus/admin**. – glenneroo Nov 03 '11 at 21:25
Thanks man - REALLY helped me. I was getting WAY too frustrated with the regular formatting. – kamran619 Oct 13 '13 at 19:45
I have been pasting my format script in every session. On windows, I had to use login.sql. glogin.sql is for Unix. – Maitus Dec 12 '18 at 23:54

APC · Answer 2 · 2011-04-24T16:38:07.800

12

SQLPlus is a simple command line tool. It's not really intended for pretty reporting. However, it does have some formatting commands, which are documented in the SQLPlus User's Guide. Find out more.

For instance you might choose to format the TITLE column to display only the first twenty characters and display the SUMMARY column in its entirety like this:

COLUMN title FORMAT a20 TRUNCATED 
COLUMN summary FORMAT a4o WORD_WRAPPED

This will allow you to see your query laid out more neatly without embedding formatting commands in its projection.

Alternatively, use an IDE such as Quest's TOAD or Oracle's own SQL Developer. These tools include a query browser which automagically displays our query results in a more pleasing grid. (Other similar tools are available).

edited Apr 24 '11 at 16:38

answered Apr 24 '11 at 16:14

APC

144,005
19
170
281

1

I remember back in school we also used SQL*Plus and there was a single command we used which made tables display nicely formatted. I can't remember what it was. And setting word wrapping for every column is not very good when you have many columns and more tables. – Richard Knop Apr 24 '11 at 16:22
@RichardKrop - I agree setting formatting for each column will get tedious. That's why it's common to use scripts for commonly run queries. I'm really not sure what that magical single omni-formatting command would have been. Perhaps it was a script which your teachers had written? – APC Apr 24 '11 at 20:32

score 10 · Answer 3 · answered May 17 '15 at 18:25

Make a script like below

#!/bin/ksh
FILE="/tmp/queryResult.csv"
sqlplus -s /nolog << !EOF!
connect username/password

SET PAGESIZE 50000
SET LINESIZE 250
SET NUMWIDTH 5
SET FEEDBACK OFF
set echo off
set heading on
set headsep off
set wrap off
SET COLSEP ","
column Title format a22
column Summary format a15

SPOOL $FILE

Select * from books;

SPOOL OFF
EXIT
!EOF!

Save script in a file namely sqlscript.sql set permission on file

chmode +x sqlscript.sql

run the script and pipe to less command

./sqlscript.sql | less -S

"S" option will allow you to scroll with arrow keys, if output is longer than columns set in terminal.

Alternatively you can download and open FILE="/tmp/queryResult.csv" in text editor of your choice.

Adjust the LINESIZE,NUMWIDTH, column character size (a22) as per your requirement

score 8 · Answer 4 · answered Apr 25 '11 at 12:29

Some may not like this advice (I can think of a few DBAs who LOVE SqlPlus), but you may want to use an IDE like Toad or SQL Developer. If you're new to Oracle, sqlplus will make you feel like you just jumped back in time! IMO, spend your time learning Oracle, not SQLPlus. (oh, and read the Concepts guide while playing around in your IDE of choice)

score 6 · Answer 5 · answered Apr 24 '11 at 17:07

Just define the column widths so that it fits the actual content of the columns

col column_name1 format a20  -- sets column to be 20 characters wide
col column_name2 format a15  -- sets column to be 15 characters wide
set line 80

select column_name1, column_name2 from books;

This should help you out.

score 5 · Answer 6 · answered Nov 22 '13 at 19:46

This can make output more pretty:

SET PAGESIZE 0
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 1000
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF SPOOL OFF
SET COLSEP ' '

Source: http://larig.wordpress.com/2011/05/29/formatting-oracle-output-in-sqlplus/

score 5 · Answer 7 · answered Apr 15 '20 at 20:27

5

Csv style, but clean:

SQL> set markup csv on

answered Apr 15 '20 at 20:27

lainatnavi

1,453
1
14
22

Please don't add [the same answer](https://stackoverflow.com/a/61237983/1324) to multiple questions. Answer the best one and flag the rest as duplicates. See [Is it acceptable to add a duplicate answer to several questions?](http://meta.stackexchange.com/q/104227/347985) – Paul Roub Apr 15 '20 at 20:33

score 2 · Answer 8 · edited Jan 04 '19 at 09:38

2

Additionally to all these answers:

set colsep "&TAB"

edited Jan 04 '19 at 09:38

barbsan

3,418
11
21
28

answered Jan 04 '19 at 09:12

Rijad Hadzic

56
4

score 1 · Answer 9 · answered Apr 24 '11 at 16:02

1

This worked for me:

SELECT ISBN, SUBSTR(TITLE, 0, 16), SUBSTR(SUMMARY, 0, 16), DATE_PUBL, PAGE_COUNT FROM books;

answered Apr 24 '11 at 16:02

Brad

603
4
12

score 0 · Answer 10 · answered Mar 23 '21 at 11:35

0

When sqlplus shows so many dashes that means your linesize is to large. at least larger than that of your console -> decrease linesize until it fits width of the console.

answered Mar 23 '21 at 11:35

user3192295

358
2
13

Ugly formatting in SQL*Plus

10 Answers10