Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have noticed that using the same SQL*Plus client to connect to different Oracle database result in different outputs for the same SQL query.

Database 1:

C:\Oracle\instantclient_10_2>sqlplus user/passwd@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(CONNECT_DATA=(SID=SID1)))'

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Aug 23 11:54:45 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select 'XXXX ',to_char(UniqueSequenceNumber,'xxxxxxxxxxxxxx') from mytable where rownum < 2;

'XXXX TO_CHAR(UNIQUES
----- ---------------
XXXX          300f483

Here the output is on one line, the headers are truncated.

Same thing on an other database:

C:\Oracle\instantclient_10_2>sqlplus user/passwd@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))(CONNECT_DATA=(SID=SID2)))'

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Aug 23 11:58:15 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> select 'XXXX ',to_char(UniqueSequenceNumber,'xxxxxxxxxxxxxx') from mytable where rownum < 2;

'XXXX'
--------------------------------
TO_CHAR(UNIQUESEQUENCENUMBER,'XXXXXXXXXXXXXX')
--------------------------------------------------------------------------------
XXXX
        3a1393b

Here the output is split on two lines.

I am not looking for SQL*Plus formatting options like linesize, column format, etc. that just impact the current session. What I need to understand is what is making the output different even when I don't specify any options.

Is there some default settings on the server side? Is it depending on the Oracle server version (10.2.0.1 in one case, 10.2.0.5 in the other)?

Note: column UniqueSequenceNumber is of type NUMBER

Note 2: Tried on a Server version 10.2.0.3, same result as on 10.2.0.1 (one liner)

In reply to miracle173 :

C:\Oracle\instantclient_10_2>sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Aug 26 09:32:02 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(CONNECT_DATA=(SID=SID1)))
Connected.
SQL> select 'xxxx' from dual;

'XXX
----
xxxx

SQL> connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))(CONNECT_DATA=(SID=SID2)))
Connected.
SQL> select 'xxxx' from dual;

'XXXX'
--------------------------------
xxxx

SQL> connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(CONNECT_DATA=(SID=SID1)))
Connected.
SQL> select 'xxxx' from dual;

'XXX
----
xxxx

SQL> connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))(CONNECT_DATA=(SID=SID2)))
Connected.
SQL> select 'xxxx' from dual;

'XXXX'
--------------------------------
xxxx
share|improve this question
    
the 10.2.0.5.0 output is strange. I tested select 'xxxx', to_char(60897595,'XXXXXXXXXXXXXX') from dual; on 10.2.0.5.0 on Solaris and got the output as in your 10.2.0.1 example. –  miracle173 Aug 23 '13 at 18:25
    
I also got the 10.2.0.1.0 output when testing on 10.2.0.4.0 on windows –  miracle173 Aug 23 '13 at 18:31
    
can you post the output of: sqlplus /nolog \n connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(CONNECT‌​_DATA=(SID=SID1))) \n select 'xxxx' from dual \n connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))(CONNECT‌​_DATA=(SID=SID2))) \n select 'xxxx' from dual; \n connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(CONNECT‌​_DATA=(SID=SID1))) \n select 'xxxx' from dual; \n connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))(CONNECT‌​_DATA=(SID=SID2))) \n select 'xxxx' from dual; –  miracle173 Aug 23 '13 at 18:43
    
Replied to you inside the question. –  neirda Aug 26 '13 at 7:38

2 Answers 2

up vote 3 down vote accepted

Tom Kyte posted something like this once and the issue was the CURSOR_SHARING parameter.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3696883368520

If it is set to FORCE at the system/database level on host2, it could be mapping/forcing the 'XXXX' hard coded value to an arbitrary 32 character variable.

share|improve this answer
    
I think you are right : SQL> select 'Y' from dual; 'Y' -------------------------------- Y SQL> alter session set cursor_sharing=exact; Session altered. SQL> select 'Y' from dual; ' - Y Thanks for finding that ! –  neirda Aug 27 '13 at 7:37

There are at least to files controlling sqlplus behavior - they are "login.sql" in current directory and "glogin.sql" in $ORACLE_HOME/sqlplus/admin/ Check those files on both servers.

Read docs for more info: http://docs.oracle.com/cd/B28359_01/server.111/b31189/ch2.htm (suitable for 10g AFAIK).

share|improve this answer
    
glogin.sql is the same on both server (and it is the default, as described here : docs.oracle.com/cd/B12037_01/server.101/b12170/ch3.htm#i1132910) –  neirda Aug 26 '13 at 10:16
    
-1 These files are read by sqlplus and are on the client machine and not on the database server. And the client is the same for both databases as mentioned in the post. –  miracle173 Aug 26 '13 at 10:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.