Home » SQL & PL/SQL » Client Tools » sqlplus output format (Oracle 11.2.0.2.0 hp-aux)
sqlplus output format [message #518705] Fri, 05 August 2011 09:19 Go to next message
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
Re: sqlplus output format [message #518712 is a reply to message #518705] Fri, 05 August 2011 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: sqlplus output format [message #518744 is a reply to message #518705] Fri, 05 August 2011 21:22 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
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.
Re: sqlplus output format [message #518912 is a reply to message #518744] Mon, 08 August 2011 07:29 Go to previous messageGo to next message
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
Re: sqlplus output format [message #518914 is a reply to message #518912] Mon, 08 August 2011 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: sqlplus output format [message #518924 is a reply to message #518914] Mon, 08 August 2011 07:52 Go to previous messageGo to next message
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
Re: sqlplus output format [message #518929 is a reply to message #518924] Mon, 08 August 2011 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
COLUMN, see the first example in my last post

Regards
Michel
Re: sqlplus output format [message #518931 is a reply to message #518929] Mon, 08 August 2011 08:14 Go to previous messageGo to next message
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
Re: sqlplus output format [message #518936 is a reply to message #518931] Mon, 08 August 2011 08:25 Go to previous messageGo to next message
cookiemonster
Messages: 11287
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.
Re: sqlplus output format [message #518942 is a reply to message #518936] Mon, 08 August 2011 08:40 Go to previous messageGo to next message
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
Re: sqlplus output format [message #518946 is a reply to message #518942] Mon, 08 August 2011 08:48 Go to previous messageGo to next message
cookiemonster
Messages: 11287
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?
Re: sqlplus output format [message #518948 is a reply to message #518946] Mon, 08 August 2011 08:53 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: sqlplus output format [message #518949 is a reply to message #518946] Mon, 08 August 2011 08:53 Go to previous messageGo to next message
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.
Re: sqlplus output format [message #518950 is a reply to message #518949] Mon, 08 August 2011 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See my previous answer.

Regards
Michel
Re: sqlplus output format [message #518956 is a reply to message #518950] Mon, 08 August 2011 09:20 Go to previous messageGo to next message
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.
Re: sqlplus output format [message #518960 is a reply to message #518956] Mon, 08 August 2011 09:25 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because there is a difference in the way you parametrize your session/instance.

Regards
Michel
Re: sqlplus output format [message #518962 is a reply to message #518960] Mon, 08 August 2011 09:30 Go to previous messageGo to next message
cookiemonster
Messages: 11287
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have you checked what cursor_sharing is set to in both DBs?
Re: sqlplus output format [message #518984 is a reply to message #518962] Mon, 08 August 2011 11:35 Go to previous messageGo to next message
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
Re: sqlplus output format [message #518985 is a reply to message #518984] Mon, 08 August 2011 11:38 Go to previous messageGo to next message
cookiemonster
Messages: 11287
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?
Re: sqlplus output format [message #518987 is a reply to message #518985] Mon, 08 August 2011 12:47 Go to previous messageGo to next message
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
Re: sqlplus output format [message #518994 is a reply to message #518987] Mon, 08 August 2011 13:18 Go to previous message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: last data changed
Next Topic: Please help me all you specialist!(3 Merged)
Goto Forum:
  


Current Time: Mon Dec 22 15:27:46 CST 2014

Total time taken to generate the page: 0.14695 seconds