Home » SQL & PL/SQL » Client Tools » SQL*PLUS format
SQL*PLUS format [message #7320] Wed, 04 June 2003 10:17 Go to next message
Vijay
Messages: 116
Registered: September 1999
Senior Member
I have set 'wrapp off' in sql*plus so I can view all my columns in a readable format but it will truncate my output.
Do you know of others parameters that must be set to be able to see all the columns in a readable format?

Thanks in advance.
Re: SQL*PLUS format [message #7323 is a reply to message #7320] Wed, 04 June 2003 12:42 Go to previous messageGo to next message
MikeT
Messages: 11
Registered: August 2002
Junior Member
try increasing your Screen Buffer Width...(Options --> Environment)
Re: SQL*PLUS format [message #7325 is a reply to message #7323] Wed, 04 June 2003 14:05 Go to previous messageGo to next message
Vijay
Messages: 116
Registered: September 1999
Senior Member
Thanks Mike, but increasing the buffer size, width or lenght does not make any difference. Any other ideas?
I have also tried page size.

Vijay.
Re: SQL*PLUS format [message #7327 is a reply to message #7325] Thu, 05 June 2003 00:05 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Perhaps this is what you are looking for:
SQL> SET LINE[SIZE] [1-32767)

look at my example:
SQL> SELECT to_char(sysdate,'DAY-MONTH-YEAR') date01
  2       , to_char(sysdate,'DAY-MONTH-YEAR') date02
  3       , to_char(sysdate,'DAY-MONTH-YEAR') date03
  4       , to_char(sysdate,'DAY-MONTH-YEAR') date04
  5       , to_char(sysdate,'DAY-MONTH-YEAR') date05
  6    from dual
  7  /

DATE01
--------------------------------------------------------------
DATE02
--------------------------------------------------------------
DATE03
--------------------------------------------------------------
DATE04
--------------------------------------------------------------
DATE05
--------------------------------------------------------------
THURSDAY -JUNE     -TWO THOUSAND THREE
THURSDAY -JUNE     -TWO THOUSAND THREE
THURSDAY -JUNE     -TWO THOUSAND THREE

DATE01
--------------------------------------------------------------
DATE02
--------------------------------------------------------------
DATE03
--------------------------------------------------------------
DATE04
--------------------------------------------------------------
DATE05
--------------------------------------------------------------
THURSDAY -JUNE     -TWO THOUSAND THREE
THURSDAY -JUNE     -TWO THOUSAND THREE

SQL> set wrap off
SQL> /
rows will be truncated

rows will be truncated

rows will be truncated

DATE01                                                         DATE02
-------------------------------------------------------------- -----------------
THURSDAY -JUNE     -TWO THOUSAND THREE                         THURSDAY -JUNE

SQL> set lines 250
SQL> /
rows will be truncated

DATE01                                                         DATE02                                                         DATE03    
-------------------------------------------------------------- -------------------------------------
THURSDAY -JUNE     -TWO THOUSAND THREE                         THURSDAY -JUNE     -TWO THOUSAND THREE                         THURSDAY

SQL> set lines 350
SQL> /

DATE01                                                         DATE02                                                         DATE03    
-------------------------------------------------------------- -------------------------------------
THURSDAY -JUNE     -TWO THOUSAND THREE                         THURSDAY -JUNE     -TWO THOUSAND THREE                         THURSDAY
HTH,
MHE
Re: SQL*PLUS format [message #7333 is a reply to message #7327] Thu, 05 June 2003 08:58 Go to previous messageGo to next message
Vijay
Messages: 116
Registered: September 1999
Senior Member
Thanks Maaher, that works perfectly fine. I can now see all my columns without any wrapping. But it seems that I will have to do this every time I launch sqlplus.

Vijay
Re: SQL*PLUS format [message #7346 is a reply to message #7333] Thu, 05 June 2003 13:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
If you add the SET LINES command to your login.sql file, it will execute automatically every time you launch SQL*Plus and you won't have to re-enter it manually.
Re: SQL*PLUS format [message #7352 is a reply to message #7346] Fri, 06 June 2003 09:10 Go to previous messageGo to next message
Vijay
Messages: 116
Registered: September 1999
Senior Member
Thanks for the hint Barbara. Definetely helpful.
Re: SQL*PLUS format [message #332088 is a reply to message #7352] Mon, 07 July 2008 08:43 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Hi,

is there any command available to set the screen buffer lenght? I know how to set it from enviornment dialogue box. But i need to set it from login.sql file.

Regards,
Dipali.
Re: SQL*PLUS format [message #332090 is a reply to message #332088] Mon, 07 July 2008 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is none.

Regards
Michel
Re: SQL*PLUS format [message #332095 is a reply to message #332090] Mon, 07 July 2008 08:54 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Okay..

Thank you michel..

Is there any way then, to set the default value for buffer screen lenght for SQL*Plus? Or i need to set it each time i open it for use?

Regards,
Dipali.

[Updated on: Mon, 07 July 2008 08:55]

Report message to a moderator

Re: SQL*PLUS format [message #332102 is a reply to message #332095] Mon, 07 July 2008 09:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know if it somewhere (surely) but if you set it once then it is kept in the next SQL*Plus sessions (for what I see).

Regards
Michel
Re: SQL*PLUS format [message #332138 is a reply to message #332095] Mon, 07 July 2008 10:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
vithalani_dipali wrote on Mon, 07 July 2008 15:54
Is there any way then, to set the default value for buffer screen lenght for SQL*Plus? Or i need to set it each time i open it for use?



Remember that we cannot see your computer. We have NO idea if you are on Linux, OS X, Unix or maybe even Windows.
Re: SQL*PLUS format [message #332214 is a reply to message #332138] Mon, 07 July 2008 22:49 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Ohh.. Sorry frank.

I am using Oracle 9.2.0.1.0, PL/SQL Release 9.2.0.1.0 and Windows XP.

Michel, The setting done for Sql*Plus screen buffer length/width is not working accorss the session.
Each time i open the Sql*Plus, i need to set it, otherwise i counldn't scroll horizontally (using mouse or arrow keys) to see the output (which is more than the screen size in).
Even like you, i also believe, there must be alteast some settings to retain the last settings done for it, which works for the rest of all the sesions.. But sadly, i couldn't find that (command or setting)..
Still searching for that.. Hope we will get it...

Regards,
Dipali..
Re: SQL*PLUS format [message #332247 is a reply to message #332214] Tue, 08 July 2008 00:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do you use sqlplus or sqlplusw? (Do you have a black or a white background?)
Re: SQL*PLUS format [message #332465 is a reply to message #7320] Tue, 08 July 2008 10:55 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B10501_01/server.920/a90842/ch3.htm
Re: SQL*PLUS format [message #332506 is a reply to message #332465] Tue, 08 July 2008 14:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hm, as I understood this thread, the question was how to store the size of your window, not settings withing SQLPLUS.
Maybe I misinterpreted.
Re: SQL*PLUS format [message #332564 is a reply to message #332506] Wed, 09 July 2008 00:30 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Hi Frank,

No dear, you didn't misinterpret i think. And my sqlplus screen is white. I am using sqlplusw.sql.

Thanks & Regards,
Dipali..

Re: SQL*PLUS format [message #332571 is a reply to message #332506] Wed, 09 July 2008 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The issue is that Windows SQL*Plus has 3 settings for end user line size:
- line size set with SQL*Plus linesize parameter
- Windows window width
- buffer width

The first can be set in login.sql
The second and third ones are recorded somewhere when you close a SQL*Plus window using "exit" and reused at the next Windows SQL*Plus starts (from my current experience).

Regards
Michel
Re: SQL*PLUS format [message #336387 is a reply to message #332571] Sat, 26 July 2008 04:29 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Yes michel ,even in my personal computer at home, i get the buffer size preserved accross the new sessions, once set.

But, on other pc, at office, i can see the previoulsy set value (in environment dialog box) of buffer width, but it is not in effect. i.e. i cannot scroll througth the screen to see the longer output lines, unless i reset the value.

Regards,
Dipali.
Re: SQL*PLUS format [message #336444 is a reply to message #7320] Sat, 26 July 2008 17:54 Go to previous messageGo to next message
Lynn T
Messages: 8
Registered: July 2008
Junior Member
I can't get my machine to revert to what it was on startup so I can't test this, but I'm almost sure I typed 'set arraysize 200' in login.sql to make this scrolling option always work. Please try and let me know. I can tell you with my login, I don't ever go into the options in sqlplus, and my screen always scrolls to the right.
Re: SQL*PLUS format [message #336452 is a reply to message #336444] Sun, 27 July 2008 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you work in which OS and which versions?

Regards
Michel
Re: SQL*PLUS format [message #337281 is a reply to message #336452] Wed, 30 July 2008 07:41 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

@Lynn,
Setting arraysize didnot solve the issue..

@Michel:
OS: Windows XP
oracle version: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production

Regards,
Dipali..
Re: SQL*PLUS format [message #337299 is a reply to message #7320] Wed, 30 July 2008 08:38 Go to previous messageGo to next message
Lynn T
Messages: 8
Registered: July 2008
Junior Member
Ok. I just did some more testing. And this has probably been stated by someone else (sorry, I didn't read everything), but I'm using sqlplusw under 10g. I have to click on 'options', then 'environment', and I've set both my buffer width and buffer length to 2000.

I've only had to do it once. From then on it always starts up that way.
Re: SQL*PLUS format [message #337301 is a reply to message #337299] Wed, 30 July 2008 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You're right, this has already been said.

Regards
Michel
Re: SQL*PLUS format [message #337370 is a reply to message #337299] Wed, 30 July 2008 12:25 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Lynn T
this has probably been stated by someone else (sorry, I didn't read everything)

Amazing use of forum features ... What algorithm do you use when picking up posts to be read?
Re: SQL*PLUS format [message #337385 is a reply to message #337370] Wed, 30 July 2008 13:11 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Lynn T's reported message

Reason I just don't get it. The last thing I read was this guy still had a problem. No, I didn't read the 25+ entries before to see what all had been suggested. I meant to, but I have my own work to do. So innocently, I decided to tell him what absolutely works for me. I actually spent at least a half-hour on just testing and typing. Why do I feel like you have to be brave to come out to this forum? There are so many snide remarks made that I want to back out, and go find someone who wants my help. I did spend some time reading the forum 'rules of conduct', and I might be wrong, but didn't it say something about being a little nice?


Right, Lynn T. It's been me who wasn't paying enough attention, not you.

For some reason, I thought that you were the original poster who found the solution by him/herself (which is fine, indeed), but - why not reading what people have said, the same people who you had asked for help?

Therefore, I sincerely apologize; I'll try to be more careful in the future and even change my nickname from Littlefoot to Littlenice (hm, no, I can't promise that). I hope that my thoughtlessly remark won't make you leave OraFAQ.
Re: SQL*PLUS format [message #337598 is a reply to message #337385] Thu, 31 July 2008 06:55 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Ohh.. I am feeling little bit bad to see that because of my post, you both got misunderstood.. Sad..
Nobody of us over here have extra time to kill.. We all are heavily loaded with our work. But still, we all are here togather just to help each other by our knowledge and experience, and thus to expand our edge of knowledge and polish the abilities by sharing it.. I appriciate each member here..

Thanks.... Smile

.
.

My problem is not still fired.. Sad
Re: SQL*PLUS format [message #337618 is a reply to message #337598] Thu, 31 July 2008 07:33 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
vithalani_dipali
Nobody of us over here have extra time to kill
Michel would love to kill men in red shirts, but was told not to do that ./fa/1626/0/.

Just kidding!

So, did you try what Lynn T suggested ("sqlplusw under 10g. I have to click on 'options', then 'environment', and I've set both my buffer width and buffer length to 2000.")?
Re: SQL*PLUS format [message #337630 is a reply to message #337618] Thu, 31 July 2008 08:07 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Laughing ...

and littlefoot, can you please clarify, who is that person in middle... Rolling Eyes


apart from jokes,

Yes, i am doing my work by setting that parameters right now..
But the problem is: Each time i start new sql*Plus window, i have to reset it to activate scrolling.

Two points i noted here.
1)Each time when i open the environment dialog box, i see the value of buffer, that i had set last time. But the strange thing is, it doesn't come into effect until i reset it..

2)Thought the scrolling is not enabled by default (when i open sql*Plus screen), when i give desc <tablename> command, the second column (i.e datatype(size)) is shown far way, that i must have to enable scrolling by restting buffer paramter from environment dialog box.

And regarding
Quote:
sqlplusw under 10g
, i have installed oracle 9i.

Regards,
Dipali...

[Updated on: Thu, 31 July 2008 08:08]

Report message to a moderator

Re: SQL*PLUS format [message #338671 is a reply to message #7320] Tue, 05 August 2008 10:51 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

These settings are stored in sqlplus.ini (the directory for Oracle Home from where the SQL*Plus exe is being run, under sqlplus\admin). The file stores the last setting when we EXIT from SQL*Plus session.

File Content

#Sql*Plus user initialzation file. DO NOT MODIFY
[WindowSize: L T R B] 0000 0000 1280 0770
[ScreenBuffer: W L] 0100 2000

[Updated on: Tue, 05 August 2008 10:56]

Report message to a moderator

Re: SQL*PLUS format [message #338789 is a reply to message #338671] Tue, 05 August 2008 23:20 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Himang,
Thanks for suggesting new way...

I came across that solution also , few days ago..
I checked that file but it is already having higher values for those parameters..
It's content is:
#Sql*Plus user initialzation file. DO NOT MODIFY
[WindowSize: L T R B] 0000 0000 1024 0704
[ScreenBuffer: W L] 1000 1000


Now, looking for another option..

Regards,
Dipali..
Re: SQL*PLUS format [message #338813 is a reply to message #338671] Wed, 06 August 2008 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
himang wrote on Tue, 05 August 2008 17:51
These settings are stored in sqlplus.ini (the directory for Oracle Home from where the SQL*Plus exe is being run, under sqlplus\admin). The file stores the last setting when we EXIT from SQL*Plus session.

File Content

#Sql*Plus user initialzation file. DO NOT MODIFY
[WindowSize: L T R B] 0000 0000 1280 0770
[ScreenBuffer: W L] 0100 2000

Nice to know. Thanks.

Regards
Michel

Re: SQL*PLUS format [message #338814 is a reply to message #338789] Wed, 06 August 2008 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
vithalani_dipali wrote on Wed, 06 August 2008 06:20
Himang,
Thanks for suggesting new way...

I came across that solution also , few days ago..
I checked that file but it is already having higher values for those parameters..
It's content is:
#Sql*Plus user initialzation file. DO NOT MODIFY
[WindowSize: L T R B] 0000 0000 1024 0704
[ScreenBuffer: W L] 1000 1000


Now, looking for another option..

Regards,
Dipali..
*
If you saw this few days ago why didn't you post it here, even if you don't think it works for you?

I suggest you to remove (or rename) the file and test again setting the buffer inside SQL*Plus and see what happens.

Regards
Michel

Re: SQL*PLUS format [message #338823 is a reply to message #338814] Wed, 06 August 2008 01:03 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Ohh.. I was working with other task, so just I missed that michel.. I would have to post that over here.. Am sorry..
Anyways,
Okay.. let me do the thing suggested by you...

Regards,
Dipali..
Re: SQL*PLUS format [message #338828 is a reply to message #338814] Wed, 06 August 2008 01:15 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

The things i did and the outcomes.....

--> I renamed the sqlplus.ini to sqlplus1.ini
--> Started sql*Plus. Horizontal scrolling was not still enabled..
--> Looked at \sqlplus\admin directory, one new file with same name (sqlplus.ini ) were created automatically (by oracle i think)
--> i changed the values of that file's last line to 100 1000 like
[ScreenBuffer: W L] 1000 1000

Still, no change..... Still the horizontal scrolling is not enabled..

Regards,
Dipali..
Re: SQL*PLUS format [message #338829 is a reply to message #7320] Wed, 06 August 2008 01:21 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Hi I tried the same things and it works for me...

I modified the file content from
Quote:
[ScreenBuffer: W L] 0100 2000

to
Quote:
[ScreenBuffer: W L] 1000 2000


and opened the new SQL*Plus window and the horizontal scrolling was enabled.

Did you opened the new SQL*Plus window after the changes? as the the INI file changes only effect new SQL*Plus window sessions..

Regards
Himanshu

[Updated on: Wed, 06 August 2008 01:21]

Report message to a moderator

Re: SQL*PLUS format [message #338842 is a reply to message #338829] Wed, 06 August 2008 01:58 Go to previous message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Yes Himanshu,

After setting those parameters, i have closed that file, and then stated new sql*plus window..
But it is not working for me..

Regards,
Dipali.
Previous Topic: SQL*Plus Compatibility (OpenVMS Client/Linux RDBMS)
Next Topic: Book(s) on Crystal Reports
Goto Forum:
  


Current Time: Sat Dec 10 09:18:53 CST 2016

Total time taken to generate the page: 0.29550 seconds