Reputation: 83755
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):
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:
Upvotes: 33
Views: 106112
Reputation: 379
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.
Upvotes: 0
Reputation: 101
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
Upvotes: 10
Reputation: 17211
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/
Upvotes: 5
Reputation: 15493
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)
Upvotes: 8
Reputation: 1088
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.
Upvotes: 6
Reputation: 146349
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).
Upvotes: 13
Reputation:
Increase the linesize, e.g SET LINESIZE 32000
or use SET WRAP OFF
(but this will truncate long values)
Upvotes: 38
Reputation: 603
This worked for me:
SELECT ISBN, SUBSTR(TITLE, 0, 16), SUBSTR(SUMMARY, 0, 16), DATE_PUBL, PAGE_COUNT FROM books;
Upvotes: 1