Home » Other » Client Tools » Formatting the result set (Oracle 12c, Windows)
icon5.gif  Formatting the result set [message #623519] Thu, 11 September 2014 03:32 Go to next message
nmdivya@gmail.com
Messages: 12
Registered: September 2014
Junior Member
Hello Everyone,

I'm planning to write an Oracle certification(SQL expert) next month and I'm preparing for it. While executing select queries in SQL*plus, I'm getting the result set in zigzag manner as below:

SQL> select * from employee;


EMP_ID EMP_DEPT EMP_FIRSTNAME
---------- --------------- ------------------------------
EMP_LASTNAME EMP_DEPT_ID BUILDING_NO
----------------------------------- --------------- -----------
ADDRESS
--------------------------------------------------------------------------------
DATE_OF_B DATE_OF_J
--------- ---------
These are only the column names and the rows in the table are getting displayed below this in the same order. Can anyone suggest what needs to be done to view the resultset look like a proper table.

Thanks,
Snowy
Re: Formatting the result set [message #623521 is a reply to message #623519] Thu, 11 September 2014 03:37 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SQL*Plus User's guide, Formatting SQL*Plus reports.

SQL> select * from emp where rownum = 1;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM
---------- ---------- --------- ---------- -------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17.12.80       1600
        20


SQL> col ename format a5
SQL> select * from emp where rownum = 1;

     EMPNO ENAME JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ----- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH CLERK           7902 17.12.80       1600                    20

SQL>
Re: Formatting the result set [message #623522 is a reply to message #623519] Thu, 11 September 2014 03:43 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
You could set the linesize higher:
SQL> set lines 500


But on your screen it still may look incorrect. If you spool to a file, however, it should be all right.

Edit: Littlefoot offers another nice suggestion. Formatting the columns is indeed a good idea!

MHE

[Updated on: Thu, 11 September 2014 03:44]

Report message to a moderator

Re: Formatting the result set [message #623527 is a reply to message #623522] Thu, 11 September 2014 04:24 Go to previous messageGo to next message
nmdivya@gmail.com
Messages: 12
Registered: September 2014
Junior Member
Hi Little foot,

Thanks for the help. I tried the command and its working.It seems tedious when I wanted to display more number of fields. I have to make more trial and error to get the correct format for each column to make it look like a table.

Cheers,
Snowy
Re: Formatting the result set [message #623528 is a reply to message #623527] Thu, 11 September 2014 04:25 Go to previous messageGo to next message
nmdivya@gmail.com
Messages: 12
Registered: September 2014
Junior Member
Hi Maaher,

The suggestion u gave works fine. Its simple and easy. Thanks for the help.

Cheers
Snowy
Re: Formatting the result set [message #623529 is a reply to message #623527] Thu, 11 September 2014 04:25 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
If it is too tedious, then you can look at Maaher's suggestion.
Re: Formatting the result set [message #623531 is a reply to message #623529] Thu, 11 September 2014 04:35 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Or switch to GUI (such as SQL Developer); it'll display horizontal scrollbar so you'll have to scroll right/left, but the result won't span multiple lines.

(Yes, there are plenty of pro et contra when comparing SQL*Plus and any GUI tool, but I'm not going to discuss that here and now. I'm talking strictly about the problem stated in the question.)
Re: Formatting the result set [message #623533 is a reply to message #623531] Thu, 11 September 2014 04:44 Go to previous messageGo to next message
nmdivya@gmail.com
Messages: 12
Registered: September 2014
Junior Member
Thanks Little foot.

SQL developer installed in my sys had got some problem, so its not opening. However, its good, I learned 2 new query statemnts from this problem.

I didnt know about the format function and the set lines before.

By the way, the links which you have provided was useful. I tried using different options in the format functions.

Thanks again.
Re: Formatting the result set [message #623555 is a reply to message #623533] Thu, 11 September 2014 07:23 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
One more thing. If you're spooling and you set the linesize rather high you might want to use trimspool:
SQL> set trimspool on


This removes trailing blanks.

MHE
Re: Formatting the result set [message #623558 is a reply to message #623555] Thu, 11 September 2014 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And if you want to post it in a forum:
set trimout on

Re: Formatting the result set [message #623562 is a reply to message #623527] Thu, 11 September 2014 07:53 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
nmdivya@gmail.com wrote on Thu, 11 September 2014 04:24

It seems tedious when I wanted to display more number of fields. I have to make more trial and error to get the correct format for each column to make it look like a table.



Well it may seem tedious but you have to keep in mind that your screen/window is only so wide and so can only place so many characters on a single line. And sqlplus is a simple, text-only, command-line interface, so does not deal with proportional fonts, etc.

It is no different than opening a text file in notepad and setting word-wrap on.
Previous Topic: how to debug with 'pl sql developer tool' in effective way
Next Topic: using product_user_profile
Goto Forum:
  


Current Time: Thu Mar 28 07:38:28 CDT 2024