Setting NLS_LANG Environment variable for Windows and Unix for Oracle Database

I am going to change the NLS_LANG value from English to Swedish as an example.

1.      Determine the current NLS_LANG value in database

SELECT * FROM V$NLS_PARAMETERS;

 

Parameter                                 Value

--------------------------------------------------------------------------

NLS_LANGUAGE                          SWEDISH

NLS_TERRITORY                            SWEDEN

NLS_CURRENCY                            Kr

NLS_ISO_CURRENCY                 SWEDEN

NLS_NUMERIC_CHARACTERS    ,.

NLS_CALENDAR                           GREGORIAN

NLS_DATE_FORMAT                 RRRR-MM-DD

NLS_DATE_LANGUAGE           SWEDISH

NLS_CHARACTERSET                WE8MSWIN1252

NLS_SORT           SWEDISH

NLS_TIME_FORMAT                 HH24:MI:SSXFF

NLS_TIMESTAMP_FORMAT                  RRRR-MM-DD HH24:MI:SSXFF

NLS_TIME_TZ_FORMAT                         HH24:MI:SSXFF TZR

NLS_TIMESTAMP_TZ_FORMAT       RRRR-MM-DD HH24:MI:SSXFF TZR

NLS_DUAL_CURRENCY             €

NLS_NCHAR_CHARACTERSET               UTF8

NLS_COMP                                             BINARY

NLS_LENGTH_SEMANTICS                     BYTE

NLS_NCHAR_CONV_EXCP                      FALSE

 

Format of the NLS_LANG value is [NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET]

In the above case value for NLS_LANG is SWEDISH_ SWEDEN. WE8MSWIN1252


2.      Set NLS_LANG Value in client

a.       Set NLS_LANG value in client (This is not permanent Solution, for permanent solution check 2.b) 

                  ALTER SESSION SET NLS_LANGUAGE = '<NLS_LANGUAGE>';

                  ALTER SESSION SET NLS_TERRITORY = '<NLS_TERRITORY>';

Example:

                  ALTER SESSION SET NLS_LANGUAGE = 'SWEDISH';

                  ALTER SESSION SET NLS_TERRITORY = 'SWEDEN';

            Check:

            SELECT USERENV('LANG') FROM DUAL;

                         USERENV(‘LANG’)

                        ------------------------------

                         S

     

SELECT * FROM V$NLS_PARAMETERS

where parameter in('NLS_LANGUAGE','NLS_TERRITORY');   

PARAMETER                   VALUE

--------------------------------------------------                                  

NLS_LANGUAGE SWEDISH

NLS_TERRITORY   SWEDEN

            

b.      Set NLS_LANG value in client (This is permanent Solution)

Windows:

                                                  i.      Go to Start-> run

                                                ii.      Type regedit and click ok

                                              iii.      Drill Down to HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE-> KEY_OraClientxxx_homeX (xxx is the oracle client version and X is the currently used home)

                                              iv.      Double click NLS_LANG and change Value data. For example in the example scenario updated NLS_LANG value to SWEDISH_SWEDEN.WE8MSWIN1252

                                                v.      Close regedit

Make sure you have backup windows registry before modifying it.     

Or

                                                      i.      Click on Computer, select Properties.

                                                      ii.      Select Advance system settings

                                                     iii.      In the Advance tab, select Environment Variables

                                                      iv.      Select New

                                                        v.      Set variable name NLS_LANG and variable value SWEDISH_SWEDEN.WE8MSWIN1252

                                                       vi.      Select ok and you should now see the new environment variable that you just created.

 Linux:

 setenv NLS_LANG <NLS_LANG>

Example: setenv NLS_LANG SWEDISH_SWEDEN. WE8MSWIN1252

3.       Restart the client. Examples, if you are using Toad restart TOAD for the changes to take effect.

Comments