3

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
  • 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
3

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.

  • 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
-1

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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