NLS_LANG Explained (How does Client-Server Character Conversion Work?)
(Doc ID 158577.1)
Last updated on OCTOBER 29, 2019
Applies to:Oracle Database - Standard Edition - Version 188.8.131.52 and later
Oracle Database - Enterprise Edition - Version 184.108.40.206 and later
Oracle Database - Personal Edition - Version 220.127.116.11 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
To provide a basic understanding of what is going on if you set NLS_LANG , how the conversion is done and how to set up a correct configuration.
If you think or notice that you have problems with character conversion the please see first <note 1628060.1> How to diagnose losing characters , getting "funny" output when inserting or selecting other than A-Z,a-z data ( = non English data like Chinese, Russian, Hebrew , insert any language here to the list that is not English) CHAR, VARCHAR2, LONG or CLOB .
To change the NLS_CHARACTERSET please see <Note 225912.1> Changing Or Choosing the Database Character Set ( NLS_CHARACTERSET )
Anyone configuring a system to handle languages other than English and want to actually know what they are doing.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document
|1.1 What is Oracle Globalization Support ?|
|1.2 What is this NLS_LANG thing anyway?|
|2.1 What is a Characterset or Code Page?|
|2.2 So Why Are There Different Charactersets?|
|2.3 What's the Difference Between 7 bit, 8 bit Charactersets and Unicode ?|
|3.1 Why Should I bother setting the correct NLS_LANG? It seams to work now.|
|3.2 A detailed example of a *wrong* nls setup to understand what's going on:|
|3.3 How to see what's really stored in the database?|
|4.1 So What Should I Do To Have A Correct Setup?|
|4.1.1 Identify the characterset/codepage used by your clients.|
|4.1.2 Set the NLS_LANG on the client to the corresponding Oracle characterset.|
|4.1.3 Create your database with a characterset that supports ALL characters used by your various clients.|
|4.1.4 Set NLS_LANG on the server ALSO to the characterset used by the OS (terminal type) of the server.|
|4.2 How can I Check the Client's NLS_LANG Setting?|
|4.2.1 On Unix:|
|4.2.2 On Windows:|
|4.3 Where is the Character Conversion Done?|
|4.4 NLS_LANG default value:|
|5.1 My windows sqlplusw.exe is not showing all my extended characters in old Oracle versions like 10G and older.|
|5.2 I get an (inverted) question mark (? or ¿) when selecting back the just inserted character.|
|5.3 What about sql*loader, import, export, my tool?|
|5.4 What about database links?|
|5.5 What about webclients (browsers) and webservers connecting to Oracle?|
|5.6 What about Multiple Homes on Windows?|
|5.7 Is there an Oracle Unicode Client on Windows?|
|5.8 UTL_FILE is writing / reading incorrect characters.|
|5.9 DBMS_LOB is not loading flat text files correctly.|
|5.10 Loading (XML) files as XMLtypes stores incorrect characters.|
|5.11 ODBC and NLS_LANG.|
|5.12 everything works except cut-and-paste from txt or word file to sqlplus:|
|5.13 What's the use of putting ( 'ENVS= NLS_LANG=....' ) in the listener.ora?|