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
jabba@thecompany.com
18
002 Simpson
Homer 382947382
homer@thecompany.com
20
003 Kent
Clark 082736194
superman@thecompany.com
5
004 Kid
Billy 928743627
billythkid@thecompany.com
9
005 Stranger
Perfect 389209831
nobody@thecompany.com
23
006 Zoidberg
Dr 094510283
crustacean@thecompany.com
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 jabba@thecompany.com
18
002 Simpson Homer
382947382 homer@thecompany.com
20
003 Kent Clark
082736194 superman@thecompany.com
5
004 Kid Billy
928743627 billythkid@thecompany.com
9
005 Stranger Perfect
389209831 nobody@thecompany.com
23
006 Zoidberg Dr
094510283 crustacean@thecompany.com
1
6 rows selected.
SQL>
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
d_dazzal@dazzal.com
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 d_dazzal@dazzal.com
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 jabba@thecompany.com 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