PK
˺[Aoa, mimetypeapplication/epub+zipPK ˺[A iTunesMetadata.plistP
This section describes new features of globalization support, provides pointers to related information in this book, and contains these topics:
Oracle Database 11g Release 2 (11.2) New Features in Globalization
Oracle Database 11g Release 1 (11.1) New Features in Globalization
Support for simplified patching of TIMESTAMP
WITH
TIMEZONE
data type values
Prior to Oracle Database 11g Release 2, patching TIMESTAMP
WITH
TIMEZONE
data values manually on the database after the time zone file had been updated was a tedious process. Now, this patching is simplified and transparent.
In addition, OCI, JDBC, Pro*C, and SQL*Plus clients can now continue to communicate with the server without having to update client-side time zone files, but there are some considerations when working in such a mixed mode.
See Also:
|
Support for Unicode 5.0, a major version of the Unicode Standard that supercedes all previous versions of the standard.
1,369 new character assignments have been made to the Unicode Standard. These additions include new characters for Cyrillic, Greek, Hebrew, Kannada, Latin, math, phonetic extensions, symbols.
New scripts have been added in Unicode 5.0: N'Ko, Balinese, Phags-pa, Phoenician, Cuneiform.
Improvements have been made in how to use characters, for example, their properties or display algorithms.
In addition to classifications for all of the new characters, a number of Southeast Asian characters have been re-classified.
Recommended Database Character Sets and Statement of Direction
A list of character sets has been compiled that Oracle strongly recommends for usage as the database character set. For new system deployment, the database character set is limited to this list of recommended character sets.
Improved performance for character set conversion.
New report section for Database Character Set Scanner that provides information about compact binary XML (CSX) data in the Data Dictionary.
GINSTALL
utility for adding customized locale files to Java components.
Three new languages added: Albanian, Belarusian, and Irish, and two new territories added: Albania and Belarus.
Linguistic index support for collation-sensitive SQL LIKE
condition.
This chapter tells how to set up a globalization support environment. It includes the following topics:
NLS (National Language Support) parameters determine the locale-specific behavior on both the client and the server. NLS parameters can be specified in the following ways:
As initialization parameters on the server
You can include parameters in the initialization parameter file to specify a default session NLS environment. These settings have no effect on the client side; they control only the server's behavior. For example:
NLS_TERRITORY = "CZECH REPUBLIC"
As environment variables on the client
You can use NLS environment variables, which may be platform-dependent, to specify locale-dependent behavior for the client and also to override the default values set for the session in the initialization parameter file. For example, on a UNIX system:
% setenv NLS_SORT FRENCH
With the ALTER SESSION
statement
You can use NLS parameters that are set in an ALTER
SESSION
statement to override the default values that are set for the session in the initialization parameter file or set by the client with environment variables.
ALTER SESSION SET NLS_SORT = FRENCH;
See Also: Oracle Database SQL Language Reference for more information about theALTER SESSION statement |
In SQL functions
You can use NLS parameters explicitly to hardcode NLS behavior within a SQL function. This practice overrides the default values that are set for the session in the initialization parameter file, set for the client with environment variables, or set for the session by the ALTER
SESSION
statement. For example:
TO_CHAR(hiredate, 'DD/MON/YYYY', 'nls_date_language = FRENCH')
See Also: Oracle Database SQL Language Reference for more information about SQL functions, including theTO_CHAR function |
Table 3-1 shows the precedence order of the different methods of setting NLS parameters. Higher priority settings override lower priority settings. For example, a default value has the lowest priority and can be overridden by any other method.
Table 3-1 Methods of Setting NLS Parameters and Their Priorities
Priority | Method |
---|---|
1 (highest) |
Explicitly set in SQL functions |
2 |
Set by an |
3 |
Set as an environment variable |
4 |
Specified in the initialization parameter file |
5 |
Default |
Table 3-2 lists the available NLS parameters. Because the SQL function NLS parameters can be specified only with specific functions, the table does not show the SQL function scope.
Table 3-2 NLS Parameters
Parameter | Description | Default | Scope:I = Initialization Parameter File E = Environment Variable A = ALTER SESSION |
---|---|---|---|
|
Calendar system |
Gregorian |
I, E, A |
|
SQL, PL/SQL operator comparison |
|
I, E, A |
|
Credit accounting symbol |
Derived from |
E |
|
Local currency symbol |
Derived from |
I, E, A |
|
Date format |
Derived from |
I, E, A |
|
Language for day and month names |
Derived from |
I, E, A |
|
Debit accounting symbol |
Derived from |
E |
|
ISO international currency symbol |
Derived from |
I, E, A |
See Also: "Choosing a Locale with the NLS_LANG Environment Variable" |
Language, territory, character set |
|
E |
|
Language |
Derived from |
I, A |
|
How strings are treated |
|
I, E, A |
|
Character that separates items in a list |
Derived from |
E |
|
Monetary symbol for dollar and cents (or their equivalents) |
Derived from |
E |
|
Reports data loss during a character type conversion |
|
I, A |
|
Decimal character and group separator |
Derived from |
I, E, A |
|
Character sort sequence |
Derived from |
I, E, A |
|
Territory |
Derived from |
I, A |
|
Timestamp |
Derived from |
I, E, A |
|
Timestamp with time zone |
Derived from |
I, E, A |
|
Dual currency symbol |
Derived from |
I, E, A |
A locale is a linguistic and cultural environment in which a system or program is running. Setting the NLS_LANG
environment parameter is the simplest way to specify locale behavior for Oracle Database software. It sets the language and territory used by the client application and the database server. It also sets the client's character set, which is the character set for data entered or displayed by a client program.
NLS_LANG
is set as an environment variable on UNIX platforms. NLS_LANG
is set in the registry on Windows platforms.
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
For example, if the Oracle Universal Installer does not populate NLS_LANG
, then its value by default is AMERICAN_AMERICA.US7ASCII
. The language is AMERICAN
, the territory is AMERICA
, and the character set is US7ASCII
. The values in NLS_LANG
and other NLS parameters are case-insensitive.
Each component of the NLS_LANG
parameter controls the operation of a subset of globalization support features:
language
Specifies conventions such as the language used for Oracle Database messages, sorting, day names, and month names. Each supported language has a unique name; for example, AMERICAN
, FRENCH
, or GERMAN
. The language argument specifies default values for the territory and character set arguments. If the language is not specified, then the value defaults to AMERICAN
.
territory
Specifies conventions such as the default date, monetary, and numeric formats. Each supported territory has a unique name; for example, AMERICA
, FRANCE
, or CANADA
. If the territory is not specified, then the value is derived from the language value.
charset
Specifies the character set used by the client application (normally the Oracle Database character set that corresponds to the user's terminal character set or the OS character set). Each supported character set has a unique acronym, for example, US7ASCII
, WE8ISO8859P1
, WE8DEC
, WE8MSWIN1252
, or JA16EUC
. Each language has a default character set associated with it.
Note: All components of theNLS_LANG definition are optional; any item that is not specified uses its default value. If you specify territory or character set, then you must include the preceding delimiter [underscore (_) for territory, period (.) for character set]. Otherwise, the value is parsed as a language name.
For example, to set only the territory portion of |
The three components of NLS_LANG
can be specified in many combinations, as in the following examples:
NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252 NLS_LANG = FRENCH_CANADA.WE8ISO8859P1 NLS_LANG = JAPANESE_JAPAN.JA16EUC
Note that illogical combinations can be set but do not work properly. For example, the following specification tries to support Japanese by using a Western European character set:
NLS_LANG = JAPANESE_JAPAN.WE8ISO8859P1
Because the WE8ISO8859P1 character set does not support any Japanese characters, you cannot store or display Japanese data if you use this definition for NLS_LANG
.
The rest of this section includes the following topics:
See Also:
|
In a UNIX operating system C-shell session, you can specify the value of NLS_LANG
by entering a statement similar to the following example:
% setenv NLS_LANG FRENCH_FRANCE.WE8ISO8859P1
Because NLS_LANG
is an environment variable, it is read by the client application at startup time. The client communicates the information defined by NLS_LANG
to the server when it connects to the database server.
The following examples show how date and number formats are affected by the NLS_LANG
parameter.
Example 3-1 Setting NLS_LANG to American_America.WE8ISO8859P1
Set NLS_LANG
so that the language is AMERICAN
, the territory is AMERICA
, and the Oracle Database character set is WE8ISO8859P1
:
% setenv NLS_LANG American_America.WE8ISO8859P1
Enter a SELECT
statement:
SQL> SELECT last_name, hire_date, ROUND(salary/8,2) salary FROM employees;
You should see results similar to the following output:
LAST_NAME HIRE_DATE SALARY ------------------------- --------- ---------- ... Sciarra 30-SEP-05 962.5 Urman 07-MAR-06 975 Popp 07-DEC-07 862.5 ...
Example 3-2 Setting NLS_LANG to French_France.WE8ISO8859P1
Set NLS_LANG
so that the language is FRENCH
, the territory is FRANCE
, and the Oracle Database character set is WE8ISO8859P1
:
% setenv NLS_LANG French_France.WE8ISO8859P1
Then the query shown in Example 3-1 returns the following output:
LAST_NAME HIRE_DATE SALARY ------------------------- --------- --------- ... Sciarra 30/09/05 962,5 Urman 07/03/06 975 Popp 07/12/07 862,5 ...
Note that the date format and the number format have changed. The numbers have not changed, because the underlying data is the same.
The NLS_LANG
parameter sets the language and territory environment used by both the server session (for example, SQL command execution) and the client application (for example, display formatting in Oracle Database tools). Using this parameter ensures that the language environments of both the database and the client application are automatically the same.
The language and territory components of the NLS_LANG
parameter determine the default values for other detailed NLS parameters, such as date format, numeric characters, and linguistic sorting. Each of these detailed parameters can be set in the client environment to override the default values if the NLS_LANG
parameter has already been set.
If the NLS_LANG
parameter is not set, then the server session environment remains initialized with values of NLS_LANGUAGE
, NLS_TERRITORY
, and other NLS instance parameters from the initialization parameter file. You can modify these parameters and restart the instance to change the defaults.
You might want to modify the NLS environment dynamically during the session. To do so, you can use the ALTER
SESSION
statement to change NLS_LANGUAGE
, NLS_TERRITORY
, and other NLS parameters.
Note: You cannot modify the setting for the client character set with theALTER SESSION statement. |
The ALTER
SESSION
statement modifies only the session environment. The local client NLS environment is not modified, unless the client explicitly retrieves the new settings and modifies its local environment.
Before Oracle Database 10g, Oracle defined language and territory definitions separately. This resulted in the definition of a territory being independent of the language setting of the user. Since Oracle Database 10g, some territories can have different date, time, number, and monetary formats based on the language setting of a user. This type of language-dependent territory definition is called a locale variant.
For the variant to work properly, both NLS_TERRITORY
and NLS_LANGUAGE
must be specified.
Table 3-3 shows the territories that have been enhanced to support variations.