'How to prettify the output coming from the SELECT query in command prompt?
I ran the simple select query in the command prompt,but the output rows are not coming in a single line. See below:
SQL> set pagesize 2000
SQL> select * from xtern_empl_rpt ;
EMP LAST_NAME
--- --------------------------------------------------
FIRST_NAME SSN
-------------------------------------------------- ---------
EMAIL_ADDR
--------------------------------------------------------------------------------
YEARS_OF_SERVICE
----------------
001 Hutt
Jabba 896743856
[email protected]
18
002 Simpson
Homer 382947382
[email protected]
20
003 Kent
Clark 082736194
[email protected]
5
004 Kid
Billy 928743627
[email protected]
9
005 Stranger
Perfect 389209831
[email protected]
23
006 Zoidberg
Dr 094510283
[email protected]
1
6 rows selected.
SQL>
Could you please help me to make each rows in a single line?
Edit
I tried below,but still is not prettified.
SQL> SET LINESIZE 4000
SQL> select * from xtern_empl_rpt ;
EMP LAST_NAME FIRST_NAME
SSN EMAIL_ADDR
YEARS_OF_SERVICE
--- -------------------------------------------------- -------------------------
------------------------- --------- --------------------------------------------
-------------------------------------------------------- ----------------
001 Hutt Jabba
896743856 [email protected]
18
002 Simpson Homer
382947382 [email protected]
20
003 Kent Clark
082736194 [email protected]
5
004 Kid Billy
928743627 [email protected]
9
005 Stranger Perfect
389209831 [email protected]
23
006 Zoidberg Dr
094510283 [email protected]
1
6 rows selected.
SQL>
Solution 1:[1]
set your column widths to fit in the screen
eg:
column EMAIL_ADDR format a30
where a is hte column width. you can use WRA to wrap the column eg
column EMAIL_ADDR format a30 WRA
or TRU to truncate, WOR to break on word boundaries
for example:
SQL> select * from emp;
ID FIRST_NAME
---------- ------------------------------
LAST_NAME
------------------------------
EMAIL_ADDR
--------------------------------------------------
1 Dazza
Smith
[email protected]
so the output is a bit tricky to read as email_addr was padded to 300 characters (as my table had it defined as varchar2(300) which sql*plus uses to format the output).
first set an appropriate linesize:
SQL> set linesize 100
now lets set the columns so they fit on one line (linesize should be greater than the total col widths):
SQL> column email_addr format a30
SQL> column last_name format a20
SQL> column first_name format a20
SQL> select * from emp;
ID FIRST_NAME LAST_NAME EMAIL_ADDR
---------- -------------------- -------------------- ------------------------------
1 Dazza Smith [email protected]
so now the columns fit easily onto a reasonably sized terminal.
in your case first_name and last_name are varchar2(50)'s yet the data in them is much smaller, so i'd start with column first_name format a15 (same for last_name). with email, your column is varchar2(100) yet the max sized output was 25 chars, so put column email format a25 for a starter.
if you did that, you should get output (if linesize is high enough) like:
SQL> select * from xtern_empl_rpt ;
EMP LAST_NAME FIRST_NAME SSN EMAIL_ADDR YEARS_OF_SERVICE
--- --------------- -------------- --------- ------------------------- ----------------
001 Hutt Jabba 896743856 [email protected] 18
finally as requested. WRA TRU and WOR. WRA is default by the way, so you dont have to use it but lets say we had:
SQL> select * from test;
A
--------------------------------------
THIS IS A SIMPLE WRAPPING TEST
but i wanted to format this as 10 characters width:
S
QL> col a format a10 WRA
SQL> select * from test;
A
----------
THIS IS A
SIMPLE WRA
PPING TEST
the WRA means just chop the string at 10 chars, regardless of whether we are in the middle of a word or not. if we wanted to break ONLY on word endings (where possible as a word > 10 still needs to break):
SQL> col a format a10 WOR
SQL> select * from test;
A
----------
THIS IS A
SIMPLE
WRAPPING
TEST
now the output is broken at word boundaries and not necessarily at 10 chars.
if we only wanted the first 10 chars and no line wrapping, we could use TRU:
SQL> col a format a10 TRU
SQL> select * from test;
A
----------
THIS IS A
Solution 2:[2]
This should fix your issue:
set wrap off
Solution 3:[3]
Try something like:
SET LINESIZE 120
(Adjust 120 to required maximum width.)
Solution 4:[4]
Before execute the select query, execute the following query to get the select query's output in CSV format. Then the output will be shown in CSV format.
set markup csv on;
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | bencripps |
| Solution 3 | |
| Solution 4 | Kaveesha Baddage |