In this post, I'll talk about 3 major topics about NLS_LANG.
- What is NLS_LANG?
- How to Set NLS_LANG in Oracle?
- What are Available Values of NLS_LANG?
- NLS_LANG Related Errors
What is NLS_LANG?
What is NLS_LANG? NLS_LANG is an environment variable in the client's environment, it's also a notation that can notify database what NLS (National Language Support) parameters should be used for current session. The format of NLS_LANG is as this:
<LANGUAGE>_<TERRITORY>.<CHARACTERSET>
There're 3 components in the environment variable NLS_LANG:
- LANGUAGE: It is equivalent to NLS_LANGUAGE in Oracle.
- TERRITORY: It is equivalent to NLS_TERRITORY in Oracle.
- CHARACTERSET: It is equivalent to NLS_CHARACTERSET in Oracle.
How to Set NLS_LANG in Oracle?
Linux and Unix
For Linux and Unix platforms, we can set a full NLS_LANG temporarily like this:
[oracle@test ~]$ export NLS_LANG=JAPANESE_JAPAN.UTF8
[oracle@test ~]$ echo $NLS_LANG
JAPANESE_JAPAN.UTF8
To permanently set NLS_LANG in Linux, we can add it to the profile.
[oracle@test ~]$ vi ~/.bash_profile
...
export NLS_LANG=JAPANESE_JAPAN.UTF8
And then take it effect immediately by this:
[oracle@test ~]$ . ~/.bash_profile
[oracle@test ~]$ echo $NLS_LANG
JAPANESE_JAPAN.UTF8
Windows
For Windows platform, we can set a full NLS_LANG temporarily like this:
C:\Users\Administrator>set NLS_LANG=JAPANESE_JAPAN.UTF8
C:\Users\Administrator>echo %NLS_LANG%
JAPANESE_JAPAN.UTF8
To permanently set NLS_LANG in Windows, we should add an environment variable.
First of all, open environment variable editor directly, then add an environment varible NLS_LANG to it.
Optional Components
All components are optional, but you have to keep the preceding delimiter for last two components.
Set Only Language
NLS_LANG=JAPANESE
Setting NLS_LANG only language will change the following NLS parameters:
- NLS_CURRENCY
- NLS_DATE_FORMAT
- NLS_DATE_LANGUAGE
- NLS_DUAL_CURRENCY
- NLS_ISO_CURRENCY
- NLS_LANGUAGE
- NLS_TERRITORY
- NLS_TIMESTAMP_FORMAT
- NLS_TIMESTAMP_TZ_FORMAT
- NLS_TIME_FORMAT
- NLS_TIME_TZ_FORMAT
Please note that, setting only language in NLS_LANG can also change session parameter NLS_TERRITORY, so as to affect NLS_DATE_FORMAT indirectly.
Set Only Territory
NLS_LANG=_JAPAN
Setting NLS_LANG only territory will change the following NLS parameters:
- NLS_CURRENCY
- NLS_DATE_FORMAT
- NLS_DUAL_CURRENCY
- NLS_ISO_CURRENCY
- NLS_TERRITORY
- NLS_TIMESTAMP_FORMAT
- NLS_TIMESTAMP_TZ_FORMAT
- NLS_TIME_FORMAT
- NLS_TIME_TZ_FORMAT
Consequently, NLS_LANGUAGE and NLS_DATE_LANGUAGE are not changed, only conventions and formats related to the territory are affected. In other words, NLS_DATE_FORMAT is directly affected by NLS_TERRITORY, not NLS_DATE_LANGUAGE.
Set Only Characterset
NLS_LANG=.UTF8
Please note that, for NLS_LANG, UTF-8 (with a hyphen) is not correct, you should use UTF8 (without hyphen) instead.
What are Available Values of NLS_LANG?
Now, let's see what values are available for those 3 components in Oracle database.
LANGUAGE
SQL> select value "NLS_LANGUAGE" from v$nls_valid_values where parameter = 'LANGUAGE' AND isdeprecated = 'FALSE' order by 1;
NLS_LANGUAGE
----------------------------------------------------------------
ALBANIAN
AMERICAN
AMHARIC
ARABIC
ARMENIAN
ASSAMESE
AZERBAIJANI
BANGLA
BASQUE
BELARUSIAN
BRAZILIAN PORTUGUESE
NLS_LANGUAGE
----------------------------------------------------------------
BULGARIAN
CANADIAN FRENCH
CATALAN
CROATIAN
CYRILLIC KAZAKH
CYRILLIC SERBIAN
CYRILLIC UZBEK
CZECH
DANISH
DARI
DIVEHI
NLS_LANGUAGE
----------------------------------------------------------------
DUTCH
EGYPTIAN
ENGLISH
ESTONIAN
FINNISH
FRENCH
GERMAN
GERMAN DIN
GREEK
GUJARATI
HEBREW
NLS_LANGUAGE
----------------------------------------------------------------
HINDI
HUNGARIAN
ICELANDIC
INDONESIAN
IRISH
ITALIAN
JAPANESE
KANNADA
KHMER
KOREAN
LAO
NLS_LANGUAGE
----------------------------------------------------------------
LATIN AMERICAN SPANISH
LATIN BOSNIAN
LATIN SERBIAN
LATIN UZBEK
LATVIAN
LITHUANIAN
MACEDONIAN
MALAY
MALAYALAM
MALTESE
MARATHI
NLS_LANGUAGE
----------------------------------------------------------------
MEXICAN SPANISH
NEPALI
NORWEGIAN
ORIYA
PERSIAN
POLISH
PORTUGUESE
PUNJABI
ROMANIAN
RUSSIAN
SIMPLIFIED CHINESE
NLS_LANGUAGE
----------------------------------------------------------------
SINHALA
SLOVAK
SLOVENIAN
SPANISH
SWAHILI
SWEDISH
TAMIL
TELUGU
THAI
TRADITIONAL CHINESE
TURKISH
NLS_LANGUAGE
----------------------------------------------------------------
TURKMEN
UKRAINIAN
URDU
VIETNAMESE
81 rows selected.
TERRITORY
SQL> select value "NLS_TERRITORY" from v$nls_valid_values where parameter = 'TERRITORY' AND isdeprecated = 'FALSE' order by 1;
NLS_TERRITORY
----------------------------------------------------------------
AFGHANISTAN
ALBANIA
ALGERIA
AMERICA
ANGOLA
ANTIGUA AND BARBUDA
ARGENTINA
ARMENIA
ARUBA
AUSTRALIA
AUSTRIA
NLS_TERRITORY
----------------------------------------------------------------
AZERBAIJAN
BAHAMAS
BAHRAIN
BANGLADESH
BARBADOS
BELARUS
BELGIUM
BELIZE
BERMUDA
BOLIVIA
BOSNIA AND HERZEGOVINA
NLS_TERRITORY
----------------------------------------------------------------
BOTSWANA
BRAZIL
BULGARIA
CAMBODIA
CAMEROON
CANADA
CATALONIA
CAYMAN ISLANDS
CHILE
CHINA
COLOMBIA
NLS_TERRITORY
----------------------------------------------------------------
CONGO BRAZZAVILLE
CONGO KINSHASA
COSTA RICA
CROATIA
CURACAO
CYPRUS
CZECH REPUBLIC
DENMARK
DJIBOUTI
DOMINICA
DOMINICAN REPUBLIC
NLS_TERRITORY
----------------------------------------------------------------
ECUADOR
EGYPT
EL SALVADOR
ESTONIA
ETHIOPIA
FINLAND
FRANCE
FYR MACEDONIA
GABON
GERMANY
GHANA
NLS_TERRITORY
----------------------------------------------------------------
GREECE
GRENADA
GUATEMALA
GUYANA
HAITI
HONDURAS
HONG KONG
HUNGARY
ICELAND
INDIA
INDONESIA
NLS_TERRITORY
----------------------------------------------------------------
IRAN
IRAQ
IRELAND
ISRAEL
ITALY
IVORY COAST
JAMAICA
JAPAN
JORDAN
KAZAKHSTAN
KENYA
NLS_TERRITORY
----------------------------------------------------------------
KOREA
KUWAIT
LAOS
LATVIA
LEBANON
LIBYA
LIECHTENSTEIN
LITHUANIA
LUXEMBOURG
MACAO
MALAWI
NLS_TERRITORY
----------------------------------------------------------------
MALAYSIA
MALDIVES
MALTA
MAURITANIA
MAURITIUS
MEXICO
MOLDOVA
MONTENEGRO
MOROCCO
MOZAMBIQUE
NAMIBIA
NLS_TERRITORY
----------------------------------------------------------------
NEPAL
NEW ZEALAND
NICARAGUA
NIGERIA
NORWAY
OMAN
PAKISTAN
PANAMA
PARAGUAY
PERU
PHILIPPINES
NLS_TERRITORY
----------------------------------------------------------------
POLAND
PORTUGAL
PUERTO RICO
QATAR
ROMANIA
RUSSIA
SAINT KITTS AND NEVIS
SAINT LUCIA
SAUDI ARABIA
SENEGAL
SERBIA
NLS_TERRITORY
----------------------------------------------------------------
SIERRA LEONE
SINGAPORE
SLOVAKIA
SLOVENIA
SOMALIA
SOUTH AFRICA
SOUTH SUDAN
SPAIN
SRI LANKA
SUDAN
SURINAME
NLS_TERRITORY
----------------------------------------------------------------
SWAZILAND
SWEDEN
SWITZERLAND
SYRIA
TAIWAN
TANZANIA
THAILAND
THE NETHERLANDS
TRINIDAD AND TOBAGO
TUNISIA
TURKEY
NLS_TERRITORY
----------------------------------------------------------------
TURKMENISTAN
UGANDA
UKRAINE
UNITED ARAB EMIRATES
UNITED KINGDOM
URUGUAY
UZBEKISTAN
VENEZUELA
VIETNAM
YEMEN
ZAMBIA
NLS_TERRITORY
----------------------------------------------------------------
ZIMBABWE
155 rows selected.
CHARACTERSET
SQL> select value "NLS_CHARACTERSET" from v$nls_valid_values where parameter = 'CHARACTERSET' AND isdeprecated = 'FALSE' order by 1;
NLS_CHARACTERSET
----------------------------------------------------------------
AL16UTF16
AL32UTF8
AR8ADOS710
AR8ADOS720
AR8APTEC715
AR8ARABICMAC
AR8ARABICMACS
AR8ASMO8X
AR8EBCDIC420S
AR8EBCDICX
AR8ISO8859P6
NLS_CHARACTERSET
----------------------------------------------------------------
AR8MSWIN1256
AR8MUSSAD768
AR8NAFITHA711
AR8NAFITHA721
AR8SAKHR706
AR8SAKHR707
AZ8ISO8859P9E
BG8MSWIN
BG8PC437S
BLT8CP921
BLT8EBCDIC1112
NLS_CHARACTERSET
----------------------------------------------------------------
BLT8EBCDIC1112S
BLT8ISO8859P13
BLT8MSWIN1257
BLT8PC775
BN8BSCII
CDN8PC863
CE8BS2000
CEL8ISO8859P14
CH7DEC
CL8BS2000
CL8EBCDIC1025
NLS_CHARACTERSET
----------------------------------------------------------------
CL8EBCDIC1025C
CL8EBCDIC1025R
CL8EBCDIC1025S
CL8EBCDIC1025X
CL8EBCDIC1158
CL8EBCDIC1158R
CL8ISO8859P5
CL8ISOIR111
CL8KOI8R
CL8KOI8U
CL8MACCYRILLIC
NLS_CHARACTERSET
----------------------------------------------------------------
CL8MACCYRILLICS
CL8MSWIN1251
D7DEC
D7SIEMENS9780X
D8BS2000
D8EBCDIC1141
D8EBCDIC273
DK7SIEMENS9780X
DK8BS2000
DK8EBCDIC1142
DK8EBCDIC277
NLS_CHARACTERSET
----------------------------------------------------------------
E7DEC
E7SIEMENS9780X
E8BS2000
EE8BS2000
EE8EBCDIC870
EE8EBCDIC870C
EE8EBCDIC870S
EE8ISO8859P2
EE8MACCE
EE8MACCES
EE8MACCROATIAN
NLS_CHARACTERSET
----------------------------------------------------------------
EE8MACCROATIANS
EE8MSWIN1250
EE8PC852
EEC8EUROASCI
EEC8EUROPA3
EL8DEC
EL8EBCDIC423R
EL8EBCDIC875
EL8EBCDIC875R
EL8GCOS7
EL8ISO8859P7
NLS_CHARACTERSET
----------------------------------------------------------------
EL8MACGREEK
EL8MACGREEKS
EL8MSWIN1253
EL8PC437S
EL8PC737
EL8PC851
EL8PC869
ET8MSWIN923
F7DEC
F7SIEMENS9780X
F8BS2000
NLS_CHARACTERSET
----------------------------------------------------------------
F8EBCDIC1147
F8EBCDIC297
HU8ABMOD
HU8CWI2
I7DEC
I7SIEMENS9780X
I8EBCDIC1144
I8EBCDIC280
IN8ISCII
IS8MACICELANDIC
IS8MACICELANDICS
NLS_CHARACTERSET
----------------------------------------------------------------
IS8PC861
IW7IS960
IW8EBCDIC1086
IW8EBCDIC424
IW8EBCDIC424S
IW8ISO8859P8
IW8MACHEBREW
IW8MACHEBREWS
IW8MSWIN1255
IW8PC1507
JA16DBCS
NLS_CHARACTERSET
----------------------------------------------------------------
JA16EBCDIC930
JA16EUC
JA16EUCTILDE
JA16EUCYEN
JA16MACSJIS
JA16SJIS
JA16SJISTILDE
JA16SJISYEN
JA16VMS
KO16DBCS
KO16KSC5601
NLS_CHARACTERSET
----------------------------------------------------------------
KO16KSCCS
KO16MSWIN949
LA8ISO6937
LA8PASSPORT
LT8MSWIN921
LT8PC772
LT8PC774
LV8PC1117
LV8PC8LR
LV8RST104090
N7SIEMENS9780X
NLS_CHARACTERSET
----------------------------------------------------------------
N8PC865
NDK7DEC
NE8ISO8859P10
NEE8ISO8859P4
NL7DEC
RU8BESTA
RU8PC855
RU8PC866
S7DEC
S7SIEMENS9780X
S8BS2000
NLS_CHARACTERSET
----------------------------------------------------------------
S8EBCDIC1143
S8EBCDIC278
SE8ISO8859P3
SF7ASCII
SF7DEC
TH8MACTHAI
TH8MACTHAIS
TH8TISASCII
TH8TISEBCDIC
TH8TISEBCDICS
TR7DEC
NLS_CHARACTERSET
----------------------------------------------------------------
TR8DEC
TR8EBCDIC1026
TR8EBCDIC1026S
TR8MACTURKISH
TR8MACTURKISHS
TR8MSWIN1254
TR8PC857
US7ASCII
US8BS2000
US8ICL
US8PC437
NLS_CHARACTERSET
----------------------------------------------------------------
UTF8
UTFE
VN8MSWIN1258
VN8VN3
WE8BS2000
WE8BS2000E
WE8BS2000L5
WE8DEC
WE8DG
WE8EBCDIC1047
WE8EBCDIC1047E
NLS_CHARACTERSET
----------------------------------------------------------------
WE8EBCDIC1140
WE8EBCDIC1140C
WE8EBCDIC1145
WE8EBCDIC1146
WE8EBCDIC1148
WE8EBCDIC1148C
WE8EBCDIC284
WE8EBCDIC285
WE8EBCDIC37
WE8EBCDIC37C
WE8EBCDIC500
NLS_CHARACTERSET
----------------------------------------------------------------
WE8EBCDIC500C
WE8EBCDIC871
WE8EBCDIC924
WE8GCOS7
WE8HP
WE8ICL
WE8ISO8859P1
WE8ISO8859P15
WE8ISO8859P9
WE8ISOICLUK
WE8MACROMAN8
NLS_CHARACTERSET
----------------------------------------------------------------
WE8MACROMAN8S
WE8MSWIN1252
WE8NCR4970
WE8NEXTSTEP
WE8PC850
WE8PC858
WE8PC860
WE8ROMAN8
YUG7ASCII
ZHS16CGB231280
ZHS16DBCS
NLS_CHARACTERSET
----------------------------------------------------------------
ZHS16GBK
ZHS16MACCGB231280
ZHS32GB18030
ZHT16BIG5
ZHT16CCDC
ZHT16DBCS
ZHT16DBT
ZHT16HKSCS
ZHT16HKSCS31
ZHT16MSWIN950
ZHT32EUC
NLS_CHARACTERSET
----------------------------------------------------------------
ZHT32SOPS
ZHT32TRIS
222 rows selected.
NLS_LANG Related Errors
Anything related to NLS parameters or conversion could generate errors, we need to set correct NLS_LANG in order to solve problems correctly, such as:
- ORA-01861: literal does not match format string
- ORA-12705: invalid or unknown NLS parameter value specified
- Export Import Character Set Conversion
- Apply Patch in Oracle Database on Windows Platform
For more information about NLS_LANG, please refer to Oracle NLS_LANG FAQ.
Please note that, NLS_DATE_FORMAT is mainly derived from NLS_TERRITORY, not NLS_DATE_LANGUAGE, this is a very crucial concept to troubleshoot globalization problems.