SQL results format differs between servers.

[smoothyc]

Here's a good one. I've been losing sleep over something I hope is very simple:

Formatting from SQL*Plus is different between Test and Prod. DB. When I execute a simple "show parameter NLS" command on test it comes back nice and neat, on production it looks as if it's wrapping.

I have looked and compared EVERY PARAMETER that I know of (the obvious ones I checked first "linesize", "pagesize").

If I change the linesize on production from 80 to 100 then the format is nice and neat (even though test's linesize is still 80). I don't want to have two different linesizes per server if possible.

The different formatting is killing my monitoring queries.

Here's an Example:
When I execute the "show paramater NLS" here are the results:

Test:

SQL> show parameter nls

NAME TYPE VALUE
------------------------------------ ----------- --------------------------
nls_calendar string
nls_comp string
nls_currency string
nls_date_format string
nls_date_language string
nls_dual_currency string
nls_iso_currency string
nls_language string AMERICAN
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string

NAME TYPE VALUE
------------------------------------ ----------- --------------------------
nls_sort string
nls_territory string AMERICA
nls_time_format string
nls_time_tz_format string
nls_timestamp_format string
nls_timestamp_tz_format string


Production:
SQL> show parameter nls

NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
nls_calendar string

nls_comp string

nls_currency string

nls_date_format string

nls_date_language string

NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------

nls_dual_currency string

nls_iso_currency string

nls_language string
AMERICAN
nls_length_semantics string
BYTE

NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
nls_nchar_conv_excp string
FALSE
nls_numeric_characters string

nls_sort string

nls_territory string
AMERICA
nls_time_format string

NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------

nls_time_tz_format string

nls_timestamp_format string

nls_timestamp_tz_format string

SQL>

[vnktummala]

may be you are formatting TYPE column for some other query. Connect to the new terminal in prod and check once.

Thanks,

[stecal]

You can check glogin.sql too.

[smoothyc]

Hey vnktummala, I'm not formatting any columns. When I login via SQL*Plus on the server, or from Oracle client, or from a third party development tool (ie. Toad) it formats the same.

My guess is there is some type of database parameter / option that is causing this. I have compare the complete init.ora parameter, set and show parameters between the servers but nothing stands out.

When I change my linesize to 132 on production (set linesize 132) before executing my query the format is perfect (yet on test the format is perfect with the linesize set to 80).

[smoothyc]

Hey Stecal -

I check the glogin.sql script and both servers have the same following settings:

-- For backward compatibility
SET PAGESIZE 14
SET SQLPLUSCOMPATIBILITY 8.1.7

-- Used by Trusted Oracle
COLUMN ROWLABEL FORMAT A15

-- Used for the SHOW ERRORS command
COLUMN LINE/COL FORMAT A8
COLUMN ERROR FORMAT A65 WORD_WRAPPED

-- Used for the SHOW SGA command
COLUMN name_col_plus_show_sga FORMAT a24

-- Defaults for SHOW PARAMETERS
COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME
COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE

-- Defaults for SET AUTOTRACE EXPLAIN report
COLUMN id_plus_exp FORMAT 990 HEADING i
COLUMN parent_id_plus_exp FORMAT 990 HEADING p
COLUMN plan_plus_exp FORMAT a60
COLUMN object_node_plus_exp FORMAT a8
COLUMN other_tag_plus_exp FORMAT a29
COLUMN other_plus_exp FORMAT a44

[smoothyc]

FOUND THE PROBLEM!!!

This was a good one. The cursor_sharing parameter were different on the two servers (production = 'similar' and test = 'exact').

There is an Oracle bug (Metalink 241464.1) that will cause formatting issue if the cursor_sharing parameter is set to 'similar'.... see below....

Thanks for your help and I hope this thread helps someone else.....


CAUSE DETERMINATION
====================
CURSOR_SHARING = SIMILAR or CURSOR_SHARING = FORCE parameter setting is causing this sqlplus formatting problem. Some code changes in 9203 patchset causes this parameter to mess up SQL*Plus formatting.

CAUSE JUSTIFICATION
====================
Note 241464.1 - TO_CHAR() , SUBSTR() AND RPAD() in SQL*Plus Display Length Too Long After Upgrading Database Version

POTENTIAL SOLUTION(S)
======================

alter session set optimizer_mode=choose;
alter session set cursor_sharing=exact;

POTENTIAL SOLUTION JUSTIFICATION(S)
====================================

Note 241464.1 - TO_CHAR() , SUBSTR() AND RPAD() in SQL*Plus Display Length Too Long After Upgrading Database Version

[PAVB]

This was a good one.

Indeed!
Thank you for sharing.