My Oracle Support Banner

NLS_LANG Explained (How does Client-Server Character Conversion Work?) (Doc ID 158577.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Standard Edition - Version 8.0.3.0 and later
Oracle Database - Enterprise Edition - Version 8.0.3.0 and later
Oracle Database - Personal Edition - Version 8.0.3.0 and later
Information in this document applies to any platform.

Purpose

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 ) 

Scope

Anyone configuring a system to handle languages other than English and want to actually know what they are doing.

Details

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
Purpose
Scope
Details
 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?
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.