Search code examples
oracleoracle11gto-charnls-lang

How to produce the same result of to_char() on the same DB instance, but from different sessions?


I have a small utility function that calculates the day of week from a given date:

select to_number(to_char(to_date('20130330', 'YYYYMMDD'),'D')) from dual;

Executing this statement on the same database instance, but from two different machines it produces two different results!

The question is: how to modify the code in order to get the same result on two different PC but using the same database instance?

Details

The result is 6 when I start a session from my pc with these nls_session parameters:

select * from nls_session_parameters;

NLS_LANGUAGE    HUNGARIAN
NLS_TERRITORY   HUNGARY
NLS_CURRENCY    Ft
NLS_ISO_CURRENCY    HUNGARY
NLS_NUMERIC_CHARACTERS  , 
NLS_CALENDAR    GREGORIAN
NLS_DATE_FORMAT RR-MON-DD
NLS_DATE_LANGUAGE   HUNGARIAN
NLS_SORT    HUNGARIAN
NLS_TIME_FORMAT HH24.MI.SSXFF
NLS_TIMESTAMP_FORMAT    RR-MON-DD HH24.MI.SSXFF
NLS_TIME_TZ_FORMAT  HH24.MI.SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT RR-MON-DD HH24.MI.SSXFF TZR
NLS_DUAL_CURRENCY   Ft
NLS_COMP    BINARY
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CONV_EXCP FALSE

The same database instance gives different result: 7 by starting a session from a different pc which has these session paramters:

PARAMETER    VALUE
NLS_LANGUAGE    AMERICAN
NLS_TERRITORY    AMERICA
NLS_CURRENCY    $
NLS_ISO_CURRENCY    AMERICA
NLS_NUMERIC_CHARACTERS    .,
NLS_CALENDAR    GREGORIAN
NLS_DATE_FORMAT    DD-MON-RR
NLS_DATE_LANGUAGE    AMERICAN
NLS_SORT    BINARY
NLS_TIME_FORMAT    HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT    HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT    DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY    $
NLS_COMP    BINARY
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CONV_EXCP    FALSE

For me the correct result is 7, but it must be provided on my PC. I've tried the following without success:

  • Altered the NLS_LANGUAGE to AMERICAN
  • Used to_char() with the optional nls_language parameter

Any suggestions?


Solution

  • The operative setting for day of week is NLS_TERRITORY.