NLS_LANG, How and Why

  • by

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:

  1. LANGUAGE: It is equivalent to NLS_LANGUAGE in Oracle.
  2. TERRITORY: It is equivalent to NLS_TERRITORY in Oracle.
  3. CHARACTERSET: It is equivalent to NLS_CHARACTERSET in Oracle.

How to Set NLS_LANG?

For Linux or Unix platform, you can set a full NLS_LANG like this:

[oracle@test ~]$ export NLS_LANG=JAPANESE_JAPAN.UTF8
[oracle@test ~]$ echo $NLS_LANG
JAPANESE_JAPAN.UTF8

For Windows platform, you can do this:

C:\Users\Administrator>set NLS_LANG=JAPANESE_JAPAN.UTF8

C:\Users\Administrator>echo %NLS_LANG%
JAPANESE_JAPAN.UTF8

All components are optional, but you have to keep the preceding delimiter with every component. For example:

1. 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_FORAMT indirectly.

2. 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.

3. 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.

Available Values

Now, let's see what values are available for those 3 components in Oracle database.

1. 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.

2. 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.

3. 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:

For more information about NLS_LANG, please refer to Oracle NLS_LANG FAQ.

Please note that, NLS_DATE_FORAMT is mainly derived from NLS_TERRITORY, not NLS_DATE_LANGUAGE, this is a very crucial concept to troubleshoot globalization problems.

Leave a Reply

Your email address will not be published. Required fields are marked *