NLS_LANG Explained (How does Client-Server Character Conversion Work?)
(Doc ID 158577.1)
Last updated on NOVEMBER 29, 2024
Applies to:
Oracle Database - Personal Edition - Version 8.0.3.0 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A 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 |