Globalization

NLS_LANG, How and Why

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 such:

<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 on every component. For example:

  1. Set Only Language:
  2. 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

    Setting NLS_LANG only by language can change NLS_TERRITORY so as to affect NLS_DATE_FORAMT in an indirectly way.

  3. Set Only Territory:
  4. 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.

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

    Leave a Reply

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