Differences and settings between Lang and nls_lang

 

What is nls_lang?

Nls_lang is an environment variable used to define the language, region, and Character Set attributes. For non-English character sets, the setting of nls_lang is very important.

NLS: 'national Language Support (NLS) 'when we set an NLS, we actually specify some expressions specific to its language when Oracle stores data, for example, if we select "Chinese", how to store Chinese characters, how to sort by rules, and how to represent the currency, the date format will be set.

Nls_lang format

Nls_lang = language_territory.charset

For common values, see Oracle Database client globalization support

Setting of nls_lang

In UNIX, nls_lang is an environment variable. In Windows, nls_lang is generally set in the registry.

Settings in UNIX

export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

In WindowsHKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEx\NLS_LANG
If there are multiple Oracle products, there may be multiple homex products, such as home0 and home1.. Generally, there is the registration item nls_lang.

 

In Windows, you can also set nls_lang to an environment variable. However, this is rarely done, but it is generally set in the registry.

Nls_lang in dB

DB also has its own nls_lang parameter to identify the language, region, and Character Set of the database. You can use the following SQL statements to check,

SELECT * FROM v$nls_parameters; ORSELECT USERENV (‘language‘) FROM DUAL;

 

The client's nls_lang and the DB's nls_lang

The nls_lang of DB should be consistent with the nls_lang in the client environment variables as much as possible.

Setting the nls_lang environment variable for Oracle databases provides a way to maintain consistency.

So why do we need to be consistent? This is because if the character set does not match, it is possible that the character conversion error may occur during the conversion between DB and client.

Setting the nls_lang environment variable for Oracle databases

Follow this procedure to set the nls_lang environment variable for Oracle databases.

To set the nls_lang environment variable for Oracle databases

1. Determine the nls_lang value.

   SELECT * FROM V$NLS_PARAMETERS

   1. Make a note of the nls_lang value, which is in the format [nls_language] _ [nls_territory]. [nls_characterset].

      For example:American_America.UTF8

   2. In the Data Warehouse database, run the command

2. For Windows:

   1. Navigate to Control Panel> system and click the Advanced Tab. Click environment variables.

   2. In system variables section, click New.

   3. In the variable name field, enterNLS_LANG.

   4. In the variable value field, enter the nls_lang value that was returned in step 1.

      The format for the nls_lang value shocould be [nls_language] _ [nls_territory]. [nls_characterset].

      For example:American_America.UTF8.

3. For UNIX, set the variable as shown below:

   Setenv nls_lang <nls_lang>

   For example:setenv NLS_LANG American_America.UTF8.

   If your data is 7-bit or 8-bit ASCII and the Informatica server is running on UNIX, then set

   NLS_LANG <NLS_LANGUAGE>_<NLS_TERRITORY>.WE8ISO8859P1

   Caution:Make sure you set the nls_lang variable correctly, as stated in this procedure, or your data will not display correctly.

4. Reboot the machine after creating the variable.

 

Lang is used to set the language, region, and Character Set of Linux.
Nls_lang is used to set the Oracle language, region, and character set.

Lang = zh_cn.gb2312
Nls_lang = American. america_zhs16gbk

 

Nls_lang = <language >_< territory>. <clients characterset>

Nls_lang = <language >_< region >_< Character Set>

 

Linux Lang settings:

[[Email protected] TMP] # locale
Lang = zh_cn.gb2312
Lc_ctype = "zh_cn.gb2312"
Lc_numeric = "zh_cn.gb2312"
Lc_time = "zh_cn.gb2312"
Lc_collate = "zh_cn.gb2312"
Lc_monetary = "zh_cn.gb2312"
Lc_messages = "zh_cn.gb2312"
Lc_paper = "zh_cn.gb2312"
Lc_name = "zh_cn.gb2312"
Lc_address = "zh_cn.gb2312"
Lc_telephone = "zh_cn.gb2312"
Lc_measurement = "zh_cn.gb2312"
Lc_identification = "zh_cn.gb2312"
Lc_all =

 

Oracle nls_lang settings:
SQL>

Select *
From database_properties
Where property_name in
('Nls _ Language', 'nls _ territory ', 'nls _ characterset ');

Property_name property_value description
--------------------------------------------------------------------------------
Nls_language American Language
Nls_territory America Territory
Nls_characterset zhs16gbk Character Set

 

 

========================= Article 2 ============================ =

1. Composition of the nls_lang Parameter
The nls_lang parameter consists of the following parts:
Nls_lang = _.
The meanings of nls_lang are as follows:
Language:
-Oracle message language
-Display month and day in date
Specified by territory
-Currency and Number Format
-Habits of regions and computing weeks and dates
Characterset:
-Control character sets used by Client Applications
Usually set or equal to the client (such as Windows) code page
Or set the Unicode application to utf8.
Run the chcp command to view the current system code page on Windows:
E: \> chcp
Active Code Page: 936
The code page 936 is also the Chinese Character Set GBK. on Microsoft's official site, we can be subject to specific encoding rules for the 936 code page. refer to the following link:
Http://www.microsoft.com/globaldev/reference/dbcs/936.htm

2. view the nls_lang Method
Windows:
Echo % nls_lang %
For example:
E: \\> echo % nls_lang %
American_america.zhs16gbk
UNIX usage:
Env | grep nls_lang
For example:
/Opt/Oracle> env | grep nls_lang
Nls_lang = american_china.zhs16gbk
Windows client settings, you can change nls_lang in the Registry, the specific key value is located in:
HKEY_LOCAL_MACHINE \ Software \ oracle \ homexx \\
XX indicates the system number when multiple ORACLE_HOME exists.

3. view the current character set parameter settings of the database
Select * from V $ nls_parameters;

4. view available Character Set parameter settings in the database
Select * from V $ nls_valid_values;

5. How to set the client nls_lang
Windows:
# Common Chinese character sets
Set nls_lang = simplified chinese_china.zhs16gbk
# Common UNICODE character sets
Set nls_lang = american_america.al32utf8
You can change the registry key value to set permanently.
HKEY_LOCAL_MACHINE \ Software \ oracle \ homexx \ nls_lang
UNIX:
# Common UNICODE character sets
Export nls_lang = american_america.al32utf8
# Common Chinese character sets
Export nls_lang = "simplified chinese_china". zhs16gbk
You can edit the bash_profile file for permanent settings.
Vi. bash_profile
Nls_lang = "simplified chinese_china". zhs16gbk export nls_lang
# Make the bash_profile settings take effect
Source. bash_profile
========================================================== ========================================================== =

[Q] how to view database character sets
[A] database server Character Set select * From nls_database_parameters, which is derived from props $ and indicates the character set of the database.
　　
Client Character Set environment select * From nls_instance_parameters, which is from V $ parameter,
　　
Indicates the character set setting of the client, which may be a parameter file, environment variable, or registry.
　　
Select * From nls_session_parameters in the session Character Set environment, which is derived from V $ nls_parameters, indicating the session's own settings, which may be the session environment variable or the session is completed by alter session. If the session has no special settings, it will be consistent with nls_instance_parameters.
　　
The character set of the client must be the same as that of the server to correctly display non-ASCII characters of the database. If multiple settings exist, alter session> environment variable> registry> parameter file
　　
The character set must be consistent, but the language settings can be different. We recommend that you use English for language settings. If the character set is zhs16gbk, The nls_lang can be american_america.zhs16gbk.
　　
[Q] how to modify the character set
　　
[A] version 8i and later can use alter database to modify the character set, but it is also limited to the subset to the superset. We do not recommend that you modify the props $ table, which may cause serious errors.
　　
Startup nomount;
Alter database Mount exclusive;
Alter system enable restricted session;
Alter system set job_queue_processes = 0;
Alter database open;
Alter database character set zhs16gbk;

[Q] how to view database character sets
[A] database server Character Set select * From nls_database_parameters, which is derived from props $ and indicates the character set of the database.
　　
Client Character Set environment select * From nls_instance_parameters, which is from V $ parameter,
　　
Indicates the character set setting of the client, which may be a parameter file, environment variable, or registry.
　　
Select * From nls_session_parameters in the session Character Set environment, which is derived from V $ nls_parameters, indicating the session's own settings, which may be the session environment variable or the session is completed by alter session. If the session has no special settings, it will be consistent with nls_instance_parameters.
　　
The character set of the client must be the same as that of the server to correctly display non-ASCII characters of the database. If multiple settings exist, alter session> environment variable> registry> parameter file
　　
The character set must be consistent, but the language settings can be different. We recommend that you use English for language settings. If the character set is zhs16gbk, The nls_lang can be american_america.zhs16gbk.
　　
[Q] how to modify the character set
　　
[A] version 8i and later can use alter database to modify the character set, but it is also limited to the subset to the superset. We do not recommend that you modify the props $ table, which may cause serious errors.
　　
Startup nomount;
Alter database Mount exclusive;
Alter system enable restricted session;
Alter system set job_queue_process = 0;
Alter database open;
Alter database character set zhs16gbk;

Turn: http://www.cnblogs.com/afant/archive/2009/01/04/1368334.html

 

This article from the "Heart force" blog, please be sure to keep this source http://xwzeng.blog.51cto.com/4833379/1559420

Differences and settings between Lang and nls_lang