snippetMinor
SQL*Plus default output format
Viewed 0 times
formatsqloutputplusdefault
Problem
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:
Here the output is on one line, the headers are truncated.
Same thing on an other database:
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 `UniqueSeque
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 300f483Here 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
3a1393bHere 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 `UniqueSeque
Solution
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.
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.
Context
StackExchange Database Administrators Q#48602, answer score: 3
Revisions (0)
No revisions yet.