OraFAQ Forum: Client Tools » sqlplus output format

Home » Other » Client Tools » sqlplus output format (Oracle 11.2.0.2.0 hp-aux)

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

sqlplus output format [message #518705]

Fri, 05 August 2011 09:19

dba_7722
Messages: 197
Registered: August 2010
Location: Delhi

Senior Member

Hello Expert,

After restart of the instance, developer are complaining that sqlplus output is changed from the database.

After the restart,when it came back most of the scripts that the user are running started having problem like the one bellow.

The user reported that all the selects are adding space to each column

Like if you do

Set colsep ','
select '11111','222222' from dual

It does result

11111 ,222222

Please suggest, what could be reason for the same.

Thanks
Jay vardhan

Report message to a moderator

Re: sqlplus output format [message #518712 is a reply to message #518705]

Fri, 05 August 2011 10:38

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

Senior Member
Account Moderator

The result is fine.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: sqlplus output format [message #518744 is a reply to message #518705]

Fri, 05 August 2011 21:22

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

Please post a copy and paste of an actual run from SQL*Plus that proves what you say. The only way that I can imagine this happening is if they used set colsep ' ,' instead of set colsep ','. So, I expect either that is the case or there is something more to it.

Report message to a moderator

Re: sqlplus output format [message #518912 is a reply to message #518744]

Mon, 08 August 2011 07:29

dba_7722
Messages: 197
Registered: August 2010
Location: Delhi

Senior Member

Hello,

Thanks for revert.

Please check the output from 1st server:


SQL>  select 'jay','suhel' from dual;

'JAY'                            'SUHEL'
-------------------------------- --------------------------------
jay                              suhel

You can see the space between the two column.

Also, please see output from the normal server:

SQL> select 'jay','suhel' from dual;

'JA 'SUHE
--- -----
jay suhel

Please suggest.

regards,
Jay vardhan

Report message to a moderator

Re: sqlplus output format [message #518914 is a reply to message #518912]

Mon, 08 August 2011 07:35

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

Senior Member
Account Moderator

SQL> col A format a3
SQL> col b format a5
SQL> select 'jay' a,'suhel' b from dual;
A   B
--- -----
jay suhel

1 row selected.

SQL> select 'jay'||' '||'suhel' c from dual;
C
---------
jay suhel

1 row selected.

Regards
Michel

Report message to a moderator

Re: sqlplus output format [message #518924 is a reply to message #518914]

Mon, 08 August 2011 07:52

dba_7722
Messages: 197
Registered: August 2010
Location: Delhi

Senior Member

Hello Michel,

I appreciate your revert. But issue is that output is adding space between the column. Do we have any parameter that is changed causing to this behavior?

Regards,
Jay vardhan

Report message to a moderator

Re: sqlplus output format [message #518929 is a reply to message #518924]

Mon, 08 August 2011 08:06

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

Senior Member
Account Moderator

COLUMN, see the first example in my last post

Regards
Michel

Report message to a moderator

Re: sqlplus output format [message #518931 is a reply to message #518929]

Mon, 08 August 2011 08:14

dba_7722
Messages: 197
Registered: August 2010
Location: Delhi

Senior Member

It is still not working. There is still space between the column output. Even with the fresh, there should not be this much between the coloumn output. That i have compared with normal server.


SQL> col A format a3
SQL> col b format a5
SQL> select 'jay','suhel' from dual;

'JAY'                            'SUHEL'
-------------------------------- --------------------------------
jay                              suhel

Report message to a moderator

Re: sqlplus output format [message #518936 is a reply to message #518931]

Mon, 08 August 2011 08:25

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

If you tell sqlplus to format columns called a and b then you need to have the query return columns called a and b.
See the aliases in Michel's example.

Report message to a moderator

Re: sqlplus output format [message #518942 is a reply to message #518936]

Mon, 08 August 2011 08:40

dba_7722
Messages: 197
Registered: August 2010
Location: Delhi

Senior Member

Hello,

Yes i agreed with your answer.

My query is about space between the column output. Without any format why it is adding space, is my only query that i need to investigate.

Please let me know if have any confusion with my query.

Regards
Jay vardhan

Report message to a moderator

Re: sqlplus output format [message #518946 is a reply to message #518942]

Mon, 08 August 2011 08:48

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

I can't get the result with spaces in any version. Though I don't have 11.2.0.2 to test on.
What's the version of the DB that works?

Report message to a moderator

Re: sqlplus output format [message #518948 is a reply to message #518946]

Mon, 08 August 2011 08:53

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

Senior Member
Account Moderator

SQL> alter session set cursor_sharing=force;

Session altered.

SQL>  select 'jay','suhel' from dual;
'JAY'                            'SUHEL'
-------------------------------- --------------------------------
jay                              suhel

1 row selected.

SQL> alter session set cursor_sharing=exact;

Session altered.

SQL> select 'jay','suhel' from dual;
'JA 'SUHE
--- -----
jay suhel

1 row selected.

Regards
Michel

Report message to a moderator

Re: sqlplus output format [message #518949 is a reply to message #518946]

Mon, 08 August 2011 08:53

dba_7722
Messages: 197
Registered: August 2010
Location: Delhi

Senior Member

Please see the output from 1st server:


SQL> select 'jay','suhel' from dual;

'JAY'                            'SUHEL'
-------------------------------- --------------------------------
jay                              suhel

now from the second server:


SQL> select 'jay','suhel' from dual;

'JA 'SUHE
--- -----
jay suhel

You can see the space between the column in the 1st output is much more than the 2nd output. Both the query is executed without any format setup. Version for both the db is 11g R2. And this behavior start from last week after restart of DB.

Report message to a moderator

Re: sqlplus output format [message #518950 is a reply to message #518949]

Mon, 08 August 2011 08:54

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

Senior Member
Account Moderator

See my previous answer.

Regards
Michel

Report message to a moderator

Re: sqlplus output format [message #518956 is a reply to message #518950]

Mon, 08 August 2011 09:20

dba_7722
Messages: 197
Registered: August 2010
Location: Delhi

Senior Member

I would like to close this call.

But please check. Without any format set why there is difference in output format for two database with same version.

Thanks

Jay vardhan.

Report message to a moderator

Re: sqlplus output format [message #518960 is a reply to message #518956]

Mon, 08 August 2011 09:25

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

Senior Member
Account Moderator

Because there is a difference in the way you parametrize your session/instance.

Regards
Michel

Report message to a moderator

Re: sqlplus output format [message #518962 is a reply to message #518960]

Mon, 08 August 2011 09:30

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

Have you checked what cursor_sharing is set to in both DBs?

Report message to a moderator

Re: sqlplus output format [message #518984 is a reply to message #518962]

Mon, 08 August 2011 11:35

dba_7722
Messages: 197
Registered: August 2010
Location: Delhi

Senior Member

Hello,

thanks for your revert.

I would like to check for the parameter that effect these output.

cursor_sharing is EXACT for which is giving normal output and one that is adding extra space is with force option.

Regards,
Jay vardhan

Report message to a moderator

Re: sqlplus output format [message #518985 is a reply to message #518984]

Mon, 08 August 2011 11:38

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

Well there you go then. Cursor sharing force does that as Michel demostrated above.
So the question is - do you really need to use force?

Report message to a moderator

Re: sqlplus output format [message #518987 is a reply to message #518985]

Mon, 08 August 2011 12:47

dba_7722
Messages: 197
Registered: August 2010
Location: Delhi

Senior Member

Hello,

Thanks expert for so long support. Issue solves after changing the parameter. Here is the demo:


SQL>  show parameter cursor_sharing

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
cursor_sharing                       string
FORCE
SQL> set colsep ','
SQL> select 'jay','suhel' from dual;

'JAY'                           ,'SUHEL'
--------------------------------,--------------------------------
jay                             ,suhel

SQL> alter session set cursor_sharing='EXACT';

Session altered.

SQL> show parameter cursor_sharing

NAME                                ,TYPE       ,VALUE
------------------------------------,-----------,------------------------------
cursor_sharing                      ,string     ,EXACT
SQL> select 'jay','suhel' from dual;

'JA,'SUHE
---,-----
jay,Suhel

thanks a lot for your support.

Jay vardhan

Report message to a moderator

Re: sqlplus output format [message #518994 is a reply to message #518987]

Mon, 08 August 2011 13:18

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

Senior Member
Account Moderator

Quote:

Issue solves after changing the parameter. Here is the demo

You should FIRST know why this parameter was set.
Changing it will change the behaviour of the applications.
Take care!

Regards
Michel

Report message to a moderator

Previous Topic:	last data changed
Next Topic:	Please help me all you specialist!(3 Merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 00:09:43 CST 2025