OraFAQ Forum: Client Tools » Formatting the result set

Home » Other » Client Tools » Formatting the result set (Oracle 12c, Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Formatting the result set [message #623519]

Thu, 11 September 2014 03:32

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

Report message to a moderator

Re: Formatting the result set [message #623521 is a reply to message #623519]

Thu, 11 September 2014 03:37

Littlefoot
Messages: 21823
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>

Report message to a moderator

Re: Formatting the result set [message #623522 is a reply to message #623519]

Thu, 11 September 2014 03:43

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

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

Report message to a moderator

Re: Formatting the result set [message #623528 is a reply to message #623527]

Thu, 11 September 2014 04:25

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

Report message to a moderator

Re: Formatting the result set [message #623529 is a reply to message #623527]

Thu, 11 September 2014 04:25

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.

Report message to a moderator

Re: Formatting the result set [message #623531 is a reply to message #623529]

Thu, 11 September 2014 04:35

Littlefoot
Messages: 21823
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.)

Report message to a moderator

Re: Formatting the result set [message #623533 is a reply to message #623531]

Thu, 11 September 2014 04:44

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.

Report message to a moderator

Re: Formatting the result set [message #623555 is a reply to message #623533]

Thu, 11 September 2014 07:23

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

Report message to a moderator

Re: Formatting the result set [message #623558 is a reply to message #623555]

Thu, 11 September 2014 07:44

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And if you want to post it in a forum:

set trimout on

Report message to a moderator

Re: Formatting the result set [message #623562 is a reply to message #623527]

Thu, 11 September 2014 07:53

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.

Report message to a moderator

Previous Topic:	how to debug with 'pl sql developer tool' in effective way
Next Topic:	using product_user_profile

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 21:47:57 CST 2025