SQL*Plus default output format

formatoracleoracle-10gsqlplus

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

Best Answer

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.