René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Globalization in Oracle

Globalization and National Language Support (nls)

Altough globalization and NLS are related, they do not refer to the same thing. NLS support is a subset of globalization support. NLS lets you choose one language and store data of that language in the database while globalization allows you to create a multilingual applications within Oracle.

Goal of globalization

The goal of globalization in Oracle is to make it possible for users to store, process and query data in their native language. This extends to
  • db utilities
  • error messages
  • sort order
  • date formats
  • time formats
  • monetary formats
  • numeric formats
  • calendar conventions

ORA_NLS33

The ORA_NLS33 environment variable points to a directory containing locale-specific data. If ORA_NLS33 is not defined, the default ORACLE_HOME/ocommon/nls/admin/data will be used.
Note, the name of this variable is ORA_NLS33 since Oracle version 8.0, in prior releases, the variable's name was ORA_NLS and ORA_NLS32.

Client character set

The client character set is set with the environment variable NLS_LANG (on windows, it's set in the registry and can be overridden by the environment variable NLS_LANG).
Whenever a client opens a connection to Oracle, an NLS environment will be created for the client which determines how locale dependent things are displayed or formatted.

SQL Functions and NLS parameters

  to_date to_number to_char to_nchar nls_upper nls_lower nlssort
NLS_DATE_LANGUAGE X X X
NLS_CALENDAR X X X
NLS_NUMERIC_CHARACTERS X X X
NLS_CURRENCY X X X
NLS_DUAL_CURRENCY X X X
NLS_ISO_CURRENCY X X X
NLS_SORT X X X X

Character sets

A character set assigns a numerical value to a character. When a database is created, a character set must be specified (it is stored in sys.props$). This character set determines which characters can be stored in the database and what characters are allowed for identifiers (for example variable names in PL/SQL programms)

European

US7ASCII ASCII 7-bit American
SF7ASCII ASCII 7-bit Finnish
YUG7ASCII ASCII 7-bit Yugoslavian
RU8BESTA BESTA 8-bit Latin/Cyrillic
EL8GCOS7 Bull EBCDIC GCOS7 8-bit Greek
WE8GCOS7 Bull EBCDIC GCOS7 8-bit West European
EL8DEC DEC 8-bit Latin/Greek
TR7DEC DEC VT100 7-bit Turkish
TR8DEC DEC 8-bit Turkish
TR8EBCDIC EBCDIC Code Page 1026 8-bit Turkish
TR8PC857 IBM-PC Code Page 857 8-bit Turkish
TR8MACTURKISH MAC Client 8-bit Turkish
TR8MACTURKISHS MAC Server 8-bit Turkish
TR8MSWIN1254 MS Windows Code Page 1254 8-bit Turkish
WE8BS2000L5 Siemens EBCDIC.DF.L5 8-bit West European/Turkish
WE8DEC DEC 8-bit West European
D7DEC DEC VT100 7-bit German
F7DEC DEC VT100 7-bit French
S7DEC DEC VT100 7-bit Swedish
E7DEC DEC VT100 7-bit Spanish
NDK7DEC DEC VT100 7-bit Norwegian/Danish
I7DEC DEC VT100 7-bit Italian
NL7DEC DEC VT100 7-bit Dutch
CH7DEC DEC VT100 7-bit Swiss (German/French)
SF7DEC DEC VT100 7-bit Finnish
WE8DG DG 8-bit West European
WE8EBCDIC37C EBCDIC Code Page 37 8-bit Oracle/c
WE8EBCDIC37 EBCDIC Code Page 37 8-bit West European
D8EBCDIC273 EBCDIC Code Page 273/1 8-bit Austrian German
DK8EBCDIC277 EBCDIC Code Page 277/1 8-bit Danish
S8EBCDIC278 EBCDIC Code Page 278/1 8-bit Swedish
I8EBCDIC280 EBCDIC Code Page 280/1 8-bit Italian
WE8EBCDIC284 EBCDIC Code Page 284 8-bit Latin American/Spanish
WE8EBCDIC285 EBCDIC Code Page 285 8-bit West European
F8EBCDIC297 EBCDIC Code Page 297 8-bit French
WE8EBCDIC500C EBCDIC Code Page 500 8-bit Oracle/c
WE8EBCDIC500 EBCDIC Code Page 500 8-bit West European
EE8EBCDIC870 EBCDIC Code Page 870 8-bit East European
WE8EBCDIC871 EBCDIC Code Page 871 8-bit Icelandic
EL8EBCDIC875 EBCDIC Code Page 875 8-bit Greek
CL8EBCDIC1025 EBCDIC Code Page 1025 8-bit Cyrillic
CL8EBCDIC1025X EBCDIC Code Page 1025 (Modified) 8-bit Cyrillic
BLT8EBCDIC1112 EBCDIC Code Page 1112 8-bit Baltic Multilingual
D8EBCDIC1141 EBCDIC Code Page 1141 8-bit Austrian German
DK8EBCDIC1142 EBCDIC Code Page 1142 8-bit Danish
S8EBCDIC1143 EBCDIC Code Page 1143 8-bit Swedish
I8EBCDIC1144 EBCDIC Code Page 1144 8-bit Italian
F8EBCDIC1147 EBCDIC Code Page 1147 8-bit French
EEC8EUROASCI EEC Targon 35 ASCI West European/Greek
EEC8EUROPA3 EEC EUROPA3 8-bit West European/Greek
LA8PASSPORT German Government Printer 8-bit All-European Latin
WE8HP HP LaserJet 8-bit West European
WE8ROMAN8 HP Roman8 8-bit West European
HU8CWI2 Hungarian 8-bit CWI-2
HU8ABMOD Hungarian 8-bit Special AB Mod
LV8RST104090 IBM-PC Alternative Code Page 8-bit Latvian (Latin/Cyrillic)
US8PC437 IBM-PC Code Page 437 8-bit American
BG8PC437S IBM-PC Code Page 437 8-bit (Bulgarian Modification)
EL8PC437S IBM-PC Code Page 437 8-bit (Greek modification)
EL8PC737 IBM-PC Code Page 737 8-bit Greek/Latin
LT8PC772 IBM-PC Code Page 772 8-bit Lithuanian (Latin/Cyrillic)
LT8PC774 IBM-PC Code Page 774 8-bit Lithuanian (Latin)
BLT8PC775 IBM-PC Code Page 775 8-bit Baltic
WE8PC850 IBM-PC Code Page 850 8-bit West European
EL8PC851 IBM-PC Code Page 851 8-bit Greek/Latin
EE8PC852 IBM-PC Code Page 852 8-bit East European
RU8PC855 IBM-PC Code Page 855 8-bit Latin/Cyrillic
WE8PC858 IBM-PC Code Page 858 8-bit West European
WE8PC860 IBM-PC Code Page 860 8-bit West European
IS8PC861 IBM-PC Code Page 861 8-bit Icelandic
CDN8PC863 IBM-PC Code Page 863 8-bit Canadian French
N8PC865 IBM-PC Code Page 865 8-bit Norwegian
RU8PC866 IBM-PC Code Page 866 8-bit Latin/Cyrillic
EL8PC869 IBM-PC Code Page 869 8-bit Greek/Latin
LV8PC1117 IBM-PC Code Page 1117 8-bit Latvian
US8ICL ICL EBCDIC 8-bit American
WE8ICL ICL EBCDIC 8-bit West European
WE8ISOICLUK ICL special version ISO8859-1
WE8ISO8859P1 ISO 8859-1 West European
EE8ISO8859P2 ISO 8859-2 East European
SE8ISO8859P3 ISO 8859-3 South European
NEE8ISO8859P4 ISO 8859-4 North and North-East European
CL8ISO8859P5 ISO 8859-5 Latin/Cyrillic
AR8ISO8859P6 ISO 8859-6 Latin/Arabic
EL8ISO8859P7 ISO 8859-7 Latin/Greek
IW8ISO8859P8 ISO 8859-8 Latin/Hebrew
NE8ISO8859P10 ISO 8859-10 North European
WE8ISO8859P15 ISO 8859-15 West European
LA8ISO6937 ISO 6937 8-bit Coded Character Set for Text Communication
IW7IS960 Israeli Standard 960 7-bit Latin/Hebrew
AR8ARABICMAC Mac Server 8-bit Latin/Arabic
EE8MACCE Mac Client 8-bit Central European
EE8MACCROATIAN Mac Client 8-bit Croatian
WE8MACROMAN8 Mac Client 8-bit Extended Roman8 West European
EL8MACGREEK Mac Client 8-bit Greek
IS8MACICELANDIC Mac Client 8-bit Icelandic
CL8MACCYRILLIC Mac Client 8-bit Latin/Cyrillic
AR8ARABICMACS Mac Server 8-bit Latin/Arabic
EE8MACCES Mac Server 8-bit Central European
EE8MACCROATIANS Mac Server 8-bit Croatian
WE8MACROMAN8S Mac Server 8-bit Extended Roman8 West European
CL8MACCYRILLICS Mac Server 8-bit Latin/Cyrillic
EL8MACGREEKS Mac Server 8-bit Greek
IS8MACICELANDICS Mac Server 8-bit Icelandic
BG8MSWIN MS Windows 8-bit Bulgarian Cyrillic
LT8MSWIN921 MS Windows Code Page 921 8-bit Lithuanian
ET8MSWIN923 MS Windows Code Page 923 8-bit Estonian
EE8MSWIN1250 MS Windows Code Page 1250 8-bit East European
CL8MSWIN1251 MS Windows Code Page 1251 8-bit Latin/Cyrillic
WE8MSWIN1252 MS Windows Code Page 1252 8-bit West European
EL8MSWIN1253 MS Windows Code Page 1253 8-bit Latin/Greek
BLT8MSWIN1257 MS Windows Code Page 1257 8-bit Baltic
BLT8CP921 Latvian Standard LVS8-92(1) Windows/Unix 8-bit Baltic
LV8PC8LR Latvian Version IBM-PC Code Page 866 8-bit Latin/Cyrillic
WE8NCR4970 NCR 4970 8-bit West European
WE8NEXTSTEP NeXTSTEP PostScript 8-bit West European
CL8KOI8R RELCOM Internet Standard 8-bit Latin/Cyrillic
US8BS2000 Siemens 9750-62 EBCDIC 8-bit American
DK8BS2000 Siemens 9750-62 EBCDIC 8-bit Danish
F8BS2000 Siemens 9750-62 EBCDIC 8-bit French
D8BS2000 Siemens 9750-62 EBCDIC 8-bit German
E8BS2000 Siemens 9750-62 EBCDIC 8-bit Spanish
S8BS2000 Siemens 9750-62 EBCDIC 8-bit Swedish
DK7SIEMENS9780X Siemens 97801/97808 7-bit Danish
F7SIEMENS9780X Siemens 97801/97808 7-bit French
D7SIEMENS9780X Siemens 97801/97808 7-bit German
I7SIEMENS9780X Siemens 97801/97808 7-bit Italian
N7SIEMENS9780X Siemens 97801/97808 7-bit Norwegian
E7SIEMENS9780X Siemens 97801/97808 7-bit Spanish
S7SIEMENS9780X Siemens 97801/97808 7-bit Swedish
WE8BS2000 Siemens EBCDIC.DF.04 8-bit West European
CL8BS2000 Siemens EBCDIC.EHC.LC 8-bit Cyrillic
AL24UTFFSS Unicode 1.1 UTF-8 Universal character set
UTF8 Unicode 2.0 UTF-8 Universal character set

Asian

BN8BSCII Bangladesh National Code 8-bit BSCII
ZHT16BIG5 BIG5 16-bit Traditional Chinese
ZHS16CGB231280 CGB2312-80 16-bit Simplified Chinese
JA16EUC EUC 24-bit Japanese
JA16EUCYEN EUC 24-bit Japanese with '\' mapped to the Japanese yen character
JA16EUCFIXED EUC 16-bit Japanese. A fixed-width subset of JA16EUC (contains only the 2-byte characters of JA16EUC). Contains no 7- or 8-bit ASCII characters
ZHT32EUC EUC 32-bit Traditional Chinese
ZHS16GBK GBK 16-bit Simplified Chinese
ZHS16GBKFIXED GBK 16-bit Simplified Chinese (16-bit fixed-width, no single byte)
ZHT16CCDC HP CCDC 16-bit Traditional Chinese
JA16DBCS IBM EBCDIC 16-bit Japanese
JA16EBCDIC930 IBM DBCS Code Page 290 16-bit Japanese
JA16DBCSFIXED IBM EBCDIC 16-bit Japanese (16-bit fixed width, no single byte)
KO16DBCS IBM EBCDIC 16-bit Korean
KO16DBCSFIXED IBM EBCDIC 16-bit Korean (16-bit fixed-width, no single byte)
ZHS16DBCS IBM EBCDIC 16-bit Simplified Chinese
ZHS16DBCSFIXED IBM EBCDIC 16-bit Simplified Chinese (16-bit fixed-width, no single byte)
ZHT16DBCS IBM EBCDIC 16-bit Traditional Chinese
KO16KSC5601 KSC5601 16-bit Korean
KO16KSCCS KSCCS 16-bit Korean
JA16VMS JVMS 16-bit Japanese
ZHS16MACCGB231280 Mac client CGB2312-80 16-bit Simplified Chinese
JA16MACSJIS Mac client Shift-JIS 16-bit Japanese
TH8MACTHAI Mac Client 8-bit Latin/Thai
TH8MACTHAIS Mac Server 8-bit Latin/Thai
ZHT16MSWIN950 MS Windows Code Page 950 Traditional Chinese
KO16MSWIN949 MS Windows Code Page 949 Korean
VN8MSWIN1258 MS Windows Code Page 1258 8-bit Vietnamese
IN8ISCII Multiple-Script Indian Standard 8-bit Latin/Indian Languages
JA16SJIS Shift-JIS 16-bit Japanese
JA16SJISFIXED Shift-JIS 16-bit Japanese. A fixed-width subset of JA16SJIS (contains only the 2-byte characters of JA16JIS). Contains no 7- or 8-bit ASCII characters
JA16SJISYEN Shift-JIS 16-bit Japanese with '\' mapped to the Japanese yen character
ZHT32SOPS SOPS 32-bit Traditional Chinese
ZHT16DBT Taiwan Taxation 16-bit Traditional Chinese
TH8TISASCII Thai Industrial Standard 620-2533 - ASCII 8-bit
TH8TISEBCDIC Thai Industrial Standard 620-2533 - EBCDIC 8-bit
ZHT32TRIS TRIS 32-bit Traditional Chinese
ZHT32TRISFIXED TRIS 32-bit Fixed-width Traditional Chinese
AL24UTFFSS Unicode 1.1 UTF-8 Universal character set
UTF8 Unicode 2.0 UTF-8 Universal character set
VN8VN3 VN3 8-bit Vietnamese

Middle East

AR8APTEC715 APTEC 715 Server 8-bit Latin/Arabic
AR8ASMO708PLUS ASMO 708 Plus 8-bit Latin/Arabic
AR8ASMO8X ASMO Extended 708 8-bit Latin/Arabic
AR8ADOS710 Arabic MS-DOS 710 Server 8-bit Latin/Arabic
AR8ADOS720 Arabic MS-DOS 720 Server 8-bit Latin/Arabic
TR7DEC DEC VT100 7-bit Turkish
TR8DEC DEC 8-bit Turkish
WE8EBCDIC37C EBCDIC Code Page 37 8-bit Oracle/c
IW8EBCDIC424 EBCDIC Code Page 424 8-bit Latin/Hebrew
WE8EBCDIC500C EBCDIC Code Page 500 8-bit Oracle/c
IW8EBCDIC1086 EBCDIC Code Page 1086 8-bit Hebrew
AR8EBCDICX EBCDIC XBASIC Server 8-bit Latin/Arabic
TR8EBCDIC1026 EBCDIC Code Page 1026 8-bit Turkish
TR8PC857 IBM-PC Code Page 857 8-bit Turkish
IW8PC1507 IBM-PC Code Page 1507/862 8-bit Latin/Hebrew
AR8ISO8859P6 ISO 8859-6 Latin/Arabic
IW8ISO8859P8 ISO 8859-8 Latin/Hebrew
WE8ISO8859P9 ISO 8859-9 West European & Turkish
LA8ISO6937 ISO 6937 8-bit Coded Character Set for Text Communication
IW7IS960 Israeli Standard 960 7-bit Latin/Hebrew
IW8MACHEBREW Mac Client 8-bit Hebrew
AR8ARABICMAC Mac Client 8-bit Latin/Arabic
TR8MACTURKISH Mac Client 8-bit Turkish
IW8MACHEBREWS Mac Server 8-bit Hebrew
AR8ARABICMACS Mac Server 8-bit Latin/Arabic
TR8MACTURKISHS Mac Server 8-bit Turkish
TR8MSWIN1254 MS Windows Code Page 1254 8-bit Turkish
IW8MSWIN1255 MS Windows Code Page 1255 8-bit Latin/Hebrew
AR8MSWIN1256 MS Windows Code Page 1256 8-Bit Latin/Arabic
IN8ISCII Multiple-Script Indian Standard 8-bit Latin/Indian Languages
AR8MUSSAD768 Mussa'd Alarabi/2 768 Server 8-bit Latin/Arabic
AR8NAFITHA711 Nafitha Enhanced 711 Server 8-bit Latin/Arabic
AR8NAFITHA721 Nafitha International 721 Server 8-bit Latin/Arabic
AR8SAKHR706 SAKHR 706 Server 8-bit Latin/Arabic
AR8SAKHR707 SAKHR 707 Server 8-bit Latin/Arabic
WE8BS2000L5 Siemens EBCDIC.DF.04.L5 8-bit West European/Turkish
AL24UTFFSS Unicode 1.1 UTF-8 Universal character set
UTF8 Unicode 2.0 UTF-8 Universal character set

Links

See also Setting NLS parameters at the database, instance and session level.