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.