Multi-language Oracle Database Support

You can set the NLS parameter at the Session, Instance, and Database levels. Session-level settings overwrite Instance-level settings.

You can set the NLS parameter at the Session, Instance, and Database levels. Session-level settings overwrite Instance-level settings.

1. NLS Parameter

Oracle called globalization support National Language Support (NLS ),

You can set the NLS parameter at the Session, Instance, and Database levels.
Session-level settings overwrite Instance-level settings, and Instance-level settings overwrite Database-level settings.
Correspondingly, Oracle provides the NLS_SESSION_PARAMETERS, NLS_INSTANCE_PARAMETERS, and NLS_DATABASE_PARAMETERS views to view the NLS parameters.
In addition, you can specify the NLS parameter in the SQL function. These functions are called locale-dependent.

A) NLS_SESSION_PARAMETERS
Parameters in this view can be set through the "alter session" statement.
Alter session set parameter_name = parameter_value [parameter_name = parameter_value]...;
If you have not set it through the "alter session" Statement, use the definition value of the NLS_LANG environment variable. (The NLS_LANG environment variables are described in detail below)
If NLS_LANG is not defined, the setting value in NLS_INSTANCE_PARAMETERS is used.
These parameters affect the Display language of the client's error information, the display format of date, number, currency, and so on, and the sorting result.

B) NLS_INSTANCE_PARAMETERS
The parameter settings in this view refer to the settings in init. ora when the database is started. You can change it using the "alter system" statement.
The NLS_LANGUAGE setting determines the language of the Server error information in alert. log and trace files.

C) NLS_DATABASE_PARAMETERS
The parameters in this view are determined when the database is created. After the database is created, it cannot be changed. do not modify the settings by modifying the system table.
If the INSTANCE and SESSION parameters are not set, the parameters here are used by default.
The NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET parameters are determined when a database is created. They cannot be overwritten by the INSTANCE and SESSION parameters. Do not try to modify the parameters after the database is created. Such an attempt will damage the database. (The two parameters are described in detail below)

NOTE 1: The NLS_DATABASE_PARAMETERS view is equivalent to SELECT name, value $ from sys. props $ where name like '% NLS % '.
Note 2: The V $ NLS_PARAMETERS view provides the current SESSION parameters and the two CHARACTERSET parameters in the NLS_DATABASE_PARAMETERS view.
NOTE 3: select userenv ('language') from dual; select sys_context ('userenv', 'language') from dual; the execution results of these two statements are equivalent
<当前session的language> _ <当前session的territory> . <数据库的字符集nls_characterset> ,
This result looks like NLS_LANG, but it is not actually. NLS_LANG defines the character set of the client.
Note 4: select userenv ('lang ') from dual; the return value is the abbreviation of the Session parameter NLS_LANGUAGE.
Note 5: The V $ NLS_VALID_VALUES view returns valid values of NLS_LANGUAGE, NLS_SORT, NLS_TERRITORY, and NLS_CHARACTERSET.

D) Locale-dependent SQL Function
SQL functions whose behavior depends on the values of NLS parameters are called locale-dependent.
SQL functions dependent on NLS parameters include:
? TO_CHAR
? TO_DATE
? TO_NUMBER
? NLS_UPPER
? NLS_LOWER
? NLS_INITCAP
? NLSSORT
In all of the preceding functions, you can specify these NLS parameters: the parameters that can be specified In the preceding functions include:
? NLS_DATE_FORMAT
? NLS_DATE_LANGUAGE
? NLS_NUMERIC_CHARACTERS
? NLS_CURRENCY
? NLS_ISO_CURRENCY
? NLS_DUAL_CURRENCY
? NLS_CALENDAR
? NLS_SORT
You can also specify the following parameters for the NLSSORT function.
? NLS_LANGUAGE
? NLS_TERRITORY
Specify parameters in the format of 'parameter = value' ['parameter = value.
For example,
Select to_char (sysdate, 'dd-MON-YYYY ', 'nls _ DATE_LANGUAGE = ''simplified chinese'') from dual; Return "-2011"
Select * from employees order by nlssort (name, 'nls _ SORT = SCHINESE_PINYIN_M ');

E) NLS_LANG
The NLS_LANG parameter is special. It is defined by the environment variable (UNIX) or registry (WINDOWS. It consists of the following three parts:
NLS_LANG = _ .
The NLS_LANGUAGE parameter of the SESSION is partially set;
_ The NLS_TERRITORY parameter of the SESSION is partially set;
. The character set of the CLIENT is partially set. It affects how to convert the character set of the client to the character set of the database.
The three parts can only define one or more parts.
If only _ Part, The default value is AMERICAN;
If only Part ,_ According Is determined by the value;
If neither _ None Part, which is equal to AMERICAN_AMERICA;
If NLS_LANG is not defined, the default value is _ . US7ASCII, where _ It is determined based on the value in NLS_INSTANCE_PARAMETERS.

The NLS_LANG parameter cannot be changed through alter session or alter system (but the NLS_LANGUAGE and NLS_TERRITORY can be changed) or defined in init. ora.
Defining this parameter during database creation does not affect database settings, including the NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET parameters of the database.
Client character set definition section of the NLS_LANG Parameter You cannot query any system tables or views.
(In fact, this parameter should only affect the client, that is, NLS_SESSION_PARAMETERS)

NLS_LANG directly sets the NLS_LANGUAGE and NLS_TERRITORY parameters, and indirectly sets other NLS parameters.
The values of NLS_DATE_LANGUAGE and NLS_SORT are determined based on NLS_LANGUAGE;
NLS_CURRENCY, NLS_DUAL_CURRENCY, NLS_ISO_CURRENCY, NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, and NLS_NUMERIC_CHARACTERS are determined based on NLS_TERRITORY.

NOTE 1: The location of NLS_LANG in the WINDOWS registry: HKEY_LOCAL_MACHINE \ SOFTWARE \ ORACLE \ KEY_xxx

NLS_INSTANCE_PARAMETERS shows the current NLS instance parameters that have been explicitly set and the values of the NLS instance parameters.
These are the settings in the init. ora of the database at the moment that the database was started or set through alter system.
If the parameter is not explicitly set in the init. ora or defined by alter system then its value is NOT derived from a "higher" parameter (we are talking about parameters like NLS_SORT that derive a default from NLS_LANGUAGE in NLS_SESSION_PARAMETERS, this is NOT the case for NLS_INSTANCE_PARAMETERS)
* NLS_LANG is not an init. ora parameter; NLS_LANGUAGE and NLS_TERRITORY are so you need to set NLS_LANGUAGE and NLS_TERRITORY separately.
* You cannot define Or NLS_LANG in the init. ora. The client characterset is defined by the NLS_LANG on the client OS (see abve ).
* You cannot define the database characterset in the init. ora. The database characterset is defined by the "Create Database" command.
* These settings take precedence above the NLS_DATABASE_PARAMETERS.
* These values are used for the NLS_SESSION_PARAMETERS if the client the NLS_LANG is NOT set.
* The NLS_LANGUAGE in the instance parameters also declares the language for the server error messages in alert. log and in trace files.

C) NLS_DATABASE_PARAMETERS
NLS_DATABASE_PARAMETERS shows the values of the NLS parameters for the database. The values are stored in the database.
Defaults to AMERICAN_AMERICA if there are no parameters explicitly set in the init. ora during database creation time. if there is parameters set in the init. ora during database creation you see them here. there is no way to change these after the database creation. do NOT attempt to update system tables to bypass these settings! These settings are used to give the database a default if the INSTANCE and SESSION parameters are not set.
The database (national) character set NLS _ (NCHAR) _ CHARACTERSET) is defined by the "Create Database" command.
* The NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET parameters cannot be overridden by instance or session parameters.
They are defined by the value specified in the "create database command and are not intended to be changed afterwards dynamically. do NOT update system tables to change the character set. this can reset upt your database and potentially make it impossible to open the database again.
* Setting the NLS_LANG during the creation of the database does not influence the NLS_DATABASE_PARAMETERS.
* The NLS_LANG set during the database creation has NO impact on the database National Characterset.