0

we have recently upgraded our test environment database to 12.2.0.1 from 11.2.0.3.

I see that NLS_TERRITORY value and NLS_LANG value in NLS_SESSION_PARAMETERS is not showing correct value after ORACLE 12C Upgrade.

It should be GERMANY and GERMAN and instead it is AMERICAN and AMERICA and hence some of the query do not show the correct result is terms of currency.

as the parameter "NLS_NUMERIC_CHARACTERS" is dependent of NLS_TERRITORY value.

I see that the NLS_DATABASE_PARAMETERS & NLS_INSTANCE_PARAMETERS, but the NLS_SESSION_PARAMETERS is wrong and hence it is affecting with some of our scripts where we are displaying the numbers.

I do not want to change it via ALTER SESSION and execute the SQL or hardcode in the SQL syntax with the parameter which handles the numbers.

Please let me know if there is any solution for the same so that NLS_SESSION_PARAMETER can be changed.

as the ORACLE DB is reading the PFILE, I have the below values set for NLS parameters are:- NLS_LANGUAGE = GERMAN NLS_TERRITORY = GERMANY nls_length_semantics = char

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
Sanjib Behera
  • 101
  • 1
  • 13

1 Answers1

0

I found the solution myself, it was a silly environment configuration and somehow reading wrong entry from another profile which is pointed by the actual profile file for the envrionment NLS_LANG. it was export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15 and hence whenever the DB was booting, the NLS session was loaded with the wrong NLS_LANG and NLS_TERRITORY value from this NLS_LANG envrionment variable. I removed that wrong profile entry and used the NLS_LANG as below:- export NLS_LANG=GERMAN_GERMANY.WE8ISO8859P15 in the actual profile file such that the DB gets the correct information.

then when I rebooted the DB, the NLS Session values gets fixed by itself and hence I do not have to change something in my scripts and will now work as same as they werr working with previous Oracle version.

Sanjib Behera
  • 101
  • 1
  • 13