Multi-language Oracle Database Support

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 you create a database. They cannot be overwritten by the INSTANCE and SESSION parameters. Do not try to modify these 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
<Language of the current Session >_< Territory of the current Session>. <database character set 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 = <Language >_< Territory>. <Charset>
<Language> the NLS_LANGUAGE parameter of the SESSION is partially set;
_ <Territory> partially sets the NLS_TERRITORY parameter of the SESSION;
The. <Charset> section sets the character set of the CLIENT. 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 the _ <Territory> part is used, the <Language> value is equal to the AMERICAN value by default;
If only the <Language> part is available, the value of _ <Territory> is determined by the value of <Language>;
If neither the <Territory> nor the <Language> part exists, the two parts are recognized as AMERICAN_AMERICA;
If NLS_LANG is not defined, the default value is <Language >_< Territory>. US7ASCII. The <Language >_< Territory> part is determined by 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.
The client character set definition section <Charset> of the NLS_LANG parameter cannot be obtained by querying any system table or view.
(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

There are 3 levels at which you can set NLS parameters: Database, Instance and Session.
If a parameter is defined at more than one level then the rules on which one takes precedence are quite straightforward:
1. NLS database settings are superseded by NLS instance settings
2. NLS database & NLS instance settings are superseded by NLS session settings

Applications can check the session, instance, and database NLS parameters by querying the following data dictionary views:
A) NLS_SESSION_PARAMETERS
NLS_SESSION_PARAMETERS shows the NLS parameters and their values for the session that is querying the view.
It does not show information about the character set.
The values of NLS parameters set by "alter session ". if there is no explicit "alter session" statement done then it reflects the setting of the corresponding NLS parameter on the client derived from the NLS_LANG variable.

The NLS_LANG parameter has three components: language, territory, and character set. Specify it in the following format, including the punctuation:
NLS_LANG = <Language >_< Territory>. <Charset>
If NLS_LANG is specified with only the <Territory> part then AMERICAN is used as default <Language>.
If NLS_LANG is specified with only the <Language> part then the <Territory> defaults to a setting based on <Language>.
If NLS_LANG is specified without the <Language >_< Territory> part then the <Language >_< Territory> part defaults to AMERICAN_AMERICA.
If the NLS_LANG is not set at all, then it defaults to <Language >_< Territory>. US7ASCII and the values for the <Language >_< Territory> part used are the ones found in
NLS_INSTANCE_PARAMETERS.

The language and territory components ofNLS_LANGParameter determine the default values for other detailed NLS parameters, such as date format, numeric characters, and linguistic sorting.
If NLS_DATE_LANGUAGE or NLS_SORT are not set then they are derived from NLS_LANGUAGE.
If NLS_CURRENCY, NLS_DUAL_CURRENCY, NLS_ISO_CURRENCY, NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, delimiter, NLS_NUMERIC_CHARACTERS are not set then they are derived from NLS_TERRITORY

* NLS_LANG cannot be changed by alter session, NLS_LANGUAGE and NLS_TERRITORY can.
* The <clients characterset> part of NLS_LANG is NOT shown in any system table or view.
* On Windows you have two possible options, normally the NLS_LANG is set in the registry, but it can also be set in the environment, however this is not often done and generally not recommended to do so. the value in the environment takes precedence over the value in the registry and is used for ALL Oracle_Homes on the server if defined as a system environment variable.
* Oracle stronugly recommends that you set the NLS_LANG on the client at least to NLS_LANG =. <clients characterset>

B) NLS_INSTANCE_PARAMETERS
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 the <clients characterset> or NLS_LANG in the init. ora. The client characterset is defined by the NLS_LANG on the client OS (see above ).
* 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.

1 2 3 Next Page