Setting NLS_LANG Parameter in Oracle

Set the NLS_LANG parameter in Oracle

NLS_LANG is set during installation from currently Oracle home or default language of the operating system.
On windows platform, its defined in registry “HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID\NLS_LANG”
On unix platform, Set environment variable as “setenv NLS_LANG FRENCH_FRANCE.WE8ISO8859P1”

NLS_LANG parameter syntax:

NLS_LANG = LANGUAGE_TERRITORY.CHARACTER_SET 

LANGUAGE : Specifies the language and conventions for displaying messages, day name, and month name. 
TERRITORY : Specifies the territory and conventions for calculating week and day numbers. 
CHARACTER_SET : Controls the character set used for displaying messages.

Example:
NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252
NLS_LANG = FRENCH_CANADA.WE8ISO8859P1
NLS_LANG = JAPANESE_JAPAN.JA16EUC

Check current value of these parameters

set line 200 pages 0
Col parameter for a20
col value for a20
select parameter,value from V$NLS_PARAMETERS where parameter in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');

Parameter            Value
------------------   -----------
NLS_LANGUAGE         AMERICAN
NLS_TERRITORY        AMERICA
NLS_CHARACTERSET     AL32UTF8

Find list of values for following parameter get from the view V$NLS_VALID_VALUES.

set line 200 pages 0
Col parameter for a20
col value for a20
select parameter,value from v$NLS_VALID_VALUES;
--Get all valid value for these parameters
NLS_LANGUAGE
NLS_SORT
NLS_TERRITORY
NLS_CHARACTERSET

Table for defining language and NLS_LANG value

Language 	         NLS_LANG Value 
Arabic                   ARABIC_UNITED ARAB EMIRATES.AR8MSWIN1256 
Brazilian Portuguese     BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1 
Bulgarian                BULGARIAN_BULGARIA.CL8MSWIN1251 
Canadian French          CANADIAN FRENCH_CANADA.WE8ISO8859P1 
Catalan                  CATALAN_CATALONIA.WE8ISO8859P1 
Croatian                 CROATIAN_CROATIA.EE8MSWIN1250 
Czech                    CZECH_CZECH REPUBLIC.EE8MSWIN1250 
Danish                   DANISH_DENMARK.WE8ISO8859P1 
Dutch                    DUTCH_THE NETHERLANDS.WE8ISO8859P1 
Egyptian                 ARABIC_UNITED ARAB EMIRATES.AR8MSWIN1256 
English                  AMERICAN_AMERICA.WE8ISO8859P1 
English(United Kingdom)  ENGLISH_UNITED KINGDOM.WE8ISO8859P1 
Estonian                 ESTONIAN_ESTONIA.BLT8MSWIN1257 
Finnish                  FINNISH_FINLAND.WE8ISO8859P1 
French                   FRENCH_FRANCE.WE8ISO8859P1 
German                   GERMAN_GERMANY.WE8ISO8859P1 
Greek                    GREEK_GREECE.EL8MSWIN1253 
Hebrew                   HEBREW_ISRAEL.IW8MSWIN1255 
Hungarian                HUNGARIAN_HUNGARY.EE8MSWIN1250 
Icelandic                ICELANDIC_ICELAND.WE8ISO8859P1 
Indonesian               INDONESIAN_INDONESIA.WE8ISO8859P1 
Italian                  ITALIAN_ITALY.WE8ISO8859P1 
Japanese                 JAPANESE_JAPAN.JA16SJIS 
Korean                   KOREAN_KOREA.KO16KSC5601 
Latin American Spanish   LATIN AMERICAN SPANISH_AMERICA.WE8ISO8859P1 
Latvian                  LATVIAN_LATVIA.BLT8MSWIN1257 
Lithuanian               LITHUANIAN_LITHUANIA.BLT8MSWIN1257 
Mexican Spanish          MEXICAN SPANISH_MEXICO.WE8ISO8859P1 
Norwegian                NORWEGIAN_NORWAY.WE8ISO8859P1 
Polish                   POLISH_POLAND.EE8MSWIN1250 
Portuguese               PORTUGUESE_PORTUGAL.WE8ISO8859P1 
Romanian                 ROMANIAN_ROMANIA.EE8MSWIN1250 
Russian                  RUSSIAN_CIS.CL8MSWIN1251 
Simplified Chinese       SIMPLIFIED CHINESE_CHINA.ZHS16GBK 
Slovak                   SLOVAK_SLOVAKIA.EE8MSWIN1250 
Spanish                  SPANISH_SPAIN.WE8ISO8859P1 
Swedish                  SWEDISH_SWEDEN.WE8ISO8859P1 
Thai                     THAI_THAILAND.TH8TISASCII 
Traditional Chinese      TRADITIONAL CHINESE_TAIWAN.ZHT16BIG5 
Turkish                  TURKISH_TURKEY.WE8ISO8859P9 
Ukrainian                UKRAINIAN_UKRAINE.CL8MSWIN1251 
Vietnamese               VIETNAMESE_VIETNAM.VN8VN3 
Others                    AMERICAN_AMERICA.US7ASCII 

Note:
NLS_DATE_FORMAT Derived from NLS_TERRITORY
NLS_DATE_LANGUAGE derived from NLS_LANGUAGE
NLS_TIMESTAMP_FORMAT derived from NLS_TERRITORY
NLS_LANGUAGE derived from NLS_LANG

For more details:
https://docs.oracle.com/cd/A84870_01/doc/nt.816/a73010/appc.htm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.