3
Setting Up a Globalization Support Environment

NLS_TIMESTAMP_FORMAT

Timestamp

Derived from NLS_TERRITORY

I, E, A

NLS_TIMESTAMP_TZ_FORMAT

Timestamp with time zone

Derived from NLS_TERRITORY

I, E, A

NLS_DUAL_CURRE NCY

Dual currency symbol

Derived from NLS_TERRITORY

I, E, A

Choosing a Locale with the NLS_LANG Environment Variable

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 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_LAN G is set as a local 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 charact er set. Specify it in the following format, including the punctuation:

NLS_LANG = langu
age_territory.charset

For example, if the Oracle Installer d oes not populate NLS_LANG, then its value by default is AMERICAN_AMERICA.US7ASCII. The language is AM ERICAN, the territory is AMERICA, and the character set is US7ASCII.

Each component of the NLS_LANG parameter controls the operation of a subset of globalization support features :

• language

  < /p>

  Specifies conventions such as the language used for Oracle messages, sorting, day names, and month names. Each sup ported language has a unique name; for example, AMERICAN, FRENCH, or GERMAN. The language argu ment specifies default values for the territory and character set arguments. If the language is not specified, then the value default s to AMERICAN.

• territory

  Specifies conventions such as the default date, monetary, and numeric formats. Each supported terri tory has a unique name; for example, AMERICA, FRANCE, or CANADA. If the territory is not speci fied, then the value is derived from the language value.

• charset

  Specifies the character set used by the client application (normally the Oracle character set that corresponds to the user's terminal character set or the OS character set). Each supported character set ha s 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 the NLS_LANG definition are optional; any item that is not specified uses its default value. If you sp ecify 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 exam ple, to set only the territory portion of NLS_LANG, use the following format: NLS_LANG=_JAPAN

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_L
ANG = 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 char acter set:

NLS_LANG = JAPANESE_JAPAN.WE8ISO8859P1

Because the WE8ISO8859P1 character set does not support any Japanese characters, you cannot store or displ ay Japanese data if you use this definition for NLS_LANG.

The rest of this sec tion includes the following topics:

• Specifying the Value of NLS_LANG
• Overr iding Language and Territory Specifications
• Locale Variants

  See Also: Appendix A, "Locale Data" for a complete list of supported languages, territories, and character sets Your operating system documentation for information about additional globalization settings that may be necessary for your platform

Specifying the Value of NLS_LANG

Set NLS_LANG as an environment variable. For example, in a UNIX operating system C-shell sess ion, you can specify the value of NLS_LANG by entering a statement similar to the following:

% setenv NLS_LANG FRENCH_FRANCE.WE8ISO8859P1

Becau se NLS_LANG is an environment variable, it is read by the client application at startup time. The client communicates th e 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.WE8ISO88 59P1

Set NLS_LANG so that the language is AMERICAN, the territory is AMERICA, and the Oracle 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:< /p>

LAST_NAME                 HIRE_DATE     SALARY
----------------------
--- --------- ----------
Sciarra                   30-SEP-97      962.5
Urman
         07-MAR-98        975
Popp                      07-DEC-99      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 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_DAT     SALARY
------------------------- -------- ----------
Sciarra
       30/09/97      962,5
Urman                     07/03/98        975
Popp
          07/12/99      862,5

Note that the date format and the n umber format have changed. The numbers have not changed, because the underlying data is the same.

Overriding Language and Territory Specificat ions

The NLS_LANG parameter sets the language and terri tory environment used by both the server session (for example, SQL command execution) and the client application (for example, displa y formatting in Oracle tools). Using this parameter ensures that the language environments of both the database and the client applic ation are automatically the same.

The language and territory components of the NLS_LA NG parameter determine the default values for other detailed NLS parameters, such as date format, numeric characters, and ling uistic sorting. Each of these detailed parameters can be set in the client environment to override the default values if the NL S_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_TERRRITORY, 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 s ession. To do so, you can use the ALTER SESSION statement to change NLS_LANGUAGE, NLS_TE RRITORY, and other NLS parameters.

The ALTER SESSION statement modifies only the session environment. The local client NLS environment is not modified, unless the client e xplicitly retrieves the new settings and modifies its local environment.

Before Oracle Database 10g Release 1 (10.1), Oracle defined language and territory definitions separately. This resulted in the definition of a territory being independent of the language setti ng of the user. In Oracle Database 10g Release 1 (10.1), some territories can have different date, time, numb er, and monetary formats based on the language setting of a user. This type of language-dependent territory definition is called a lo cale variant.

For the variant to work properly, both NLS_TERRITORY and N LS_LANGUAGE must be specified. For example, if NLS_LANGUAGE is specified as DUTCH and NLS_TERR ITORY is not set, then the territory behavior is THE NETHERLANDS. If NLS_TERRITORY is set to B ELGIUM and NLS_LANGUAGE is not set or it is set to DUTCH, then date, time, number, and monetary form ats are based on DUTCH behavior. By contrast, if NLS_TERRITORY is set to BELGIUM and NLS _LANGUAGE is set to FRENCH, then date, time, number, and monetary formats are based on FRENCH behavi or.

Table 3-3 shows the territories that have been enhanced to support variations. Default territory behaviors are noted. They occur when NLS_LANGUAGE is not specified.

Table 3-3 Oracle Locale Variants  

Should the NLS_LANG Setting Match the Database Character Set?

The NLS_LANG character set should reflect the setting of the operating system character set of the client. For example, if the database character set is AL32UTF8 and the client is running on a Windows operating system, then y ou should not set AL32UTF8 as the client character set in the NLS_LANG parameter because there are no UTF-8 WIN32 client s. Instead the NLS_LANG setting should reflect the code page of the client. For example, on an English Windows client, t he code page is 1252. An appropriate setting for NLS_LANG is AMERICAN_AMERICA.WE8MSWIN1252.

Setting NLS_LANG correctly allows proper conversion from the client operating system characte r set to the database character set. When these settings are the same, Oracle assumes that the data being sent or received is encoded in the same character set as the database character set, so no validation or conversion is performed. This can lead to corrupt data if the client code page and the database character set are different and conversions are necessary.

NLS Database Parame ters

When a new database is created during the execution of the CREATE DATABASE statement, the NLS-related database configuration is established. The current NLS instance para meters are stored in the data dictionary along with the database and national character sets. The NLS instance parameters are read fr om the initialization parameter file at instance startup.

You can find the values for NLS p arameters by using:

• NLS Data Di ctionary Views
• NLS Dynamic Performance View s
• OCINlsGetInfo() Function

NLS Data Dictio nary Views

Applications can check the session, instance, and databas e NLS parameters by querying the following data dictionary views:

• 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.
• NLS_INSTANCE_PARAMETERS shows the current NLS instance parameters that have been explicitly set and the values of the NLS instance parameters.
• NLS_DATABASE_PARAMETERS shows the values of the NLS parameters for the dat abase. The values are stored in the database.

NLS_LANGUAGE = FRENCH

ORA-00942:  table or view does not exist

ORA-00942:  table ou vue inexistante

<product_id><
;language_abbrev>.MSB

ALTER SESSION SET NLS_LANGUAGE=Ital
ian;

<
a name="1006539">SQL> SELECT last_name, hire_date, ROUND(salary/8,2) salary FROM employees;

LAST_NAM
E                 HIRE_DATE     SALARY
------------------------- --------- ----------
Sci
arra                   30-SET-97      962.5
Urman                     07-MAR-98        975
Popp                      07-DIC-99      862.5

SQL> ALTER SESSION SET NLS_LANGUAGE=German;

SQL> SELECT last_name, hire_date, ROUND(salary/8,2) salary FROM employees;

LAST_NAME
              HIRE_DATE     SALARY
------------------------- --------- ----------
Sciarra
                   30-SEP-97      962.5
Urman                     07-MÄR-98        975
<
/a>Popp                      07-DEZ-99      862.5

NLS_TERRITORY = FRANCE

ALTER SESSION SET NLS_TERRITORY
 = France;

SQL> SELECT TO_CHAR(s
alary,'L99G999D99') salary FROM employees;

SALARY
--------------------

$24,000.00
$17,000.00
$17,000.00

ALTER SESSION SET NLS_TERRITORY = Germany;
Session altered.

SQL> SELECT
 TO_CHAR(salary,'L99G999D99') salary FROM employees;

SALARY
--------------------
€24.000,00
€17.000,00
€17.000,00

ALTER SESSION S
ET NLS_LANGUAGE = German;
Sitzung wurde geändert.

SQL> SELECT TO_CHAR(salary,'L99G999D99') salary FROM employ
ees;

SALARY
--------------------
€24.000,00
€17.000,00
€17.000,00

ALTER SESSION SET NLS_TERRITORY = America;
Sitzung wurde geändert.

SQL> SELECT TO_CHAR(salary,'L99G999D99') salary FROM employees;

SAL
ARY
--------------------
$24,000.00
$17,000.00
$17,000.00

% setenv NLS_LANG Italian_Italy.WE8DEC

SQL> SELECT last_name, hire_date, ROUND(salary/8,2) salary FROM employ
ees;

LAST_NAME                 HIRE_DATE     SALARY
------------------------- --------
- ----------
Sciarra                   30-SET-97      962,5
Urman                     07-
MAR-98        975
Popp                      07-DIC-99      862,5

SQL> ALTER SESSION SET NLS_LANGUAGE=german;

Session wurde geändert.

SQL> ALTER SESSION SET NLS_DATE_FORMAT='D
D.MON.YY';

Session wurde geändert.

SQL
> ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,';

Session wurde geändert.

SQL> SELECT last_name, hire_date, ROUND(salary/8,2) salary
 FROM employees;

LAST_NAME                 HIRE_DATE     SALARY
----------------------
--- --------- ----------
Sciarra                   30.SEP.97      962.5
Urman
         07.MÄR.98        975
Popp                      07.DEZ.99      862.5

ALTER SESSION SET NLS_DATE_LANGUAGE = FRENCH

SELECT TO_CHAR(SYSDATE, 'Day:Dd Month yyyy') FROM
dual;

TO_CHAR(SYSDATE,'DAY:DDMONTHYYYY')
---------------------------------------------
---------------
Vendredi:07 Décembre  2001

SQL> SELECT TO_CHAR(TO_DATE('12-Oct-20
01'),'Day: ddspth Month') FROM dual;

TO_CHAR(TO_DATE('12-OCT-2001'),'DAY:DDSPTHMONTH')
--------------------------------------------------------------------
Vendredi: twelfth Octobre

SELECT TO_CHAR(SYSDATE, 'Dy:d
d Mon yyyy') FROM dual;

TO_CHAR(SYSDATE,'DY:DDMO
------------------------
Ve:07 Dec 2001

INSERT INTO tablename VALUES ('12-Fév-1997')
;

NLS_TIMESTAMP_FORMAT  = 'YYYY-MM-DD HH:MI:SS.FF'

SQL> SELECT TO_TIMESTAMP('11-nov-2000 01:00:00.336', 'dd-mon-yyyy hh:mi:ss.ff')

<
/a>FROM dual;

TO_TIMESTAMP('11-NOV-200001:00:00.336','DD-MON-YYYYHH:MI:SS.FF')
--------
-------------------------------------------------------------------
11-NOV-00 01:00:00.336000000

SQL> SELECT TO_TIMESTAMP_TZ('2000-08-20
, 05:00:00.55 America/Los_Angeles', 
'yyyy-mm-dd hh:mi:ss.ff TZR') FROM dual;

TO_TIMESTAMP_TZ('2000-08-20,05
:00:00.55AMERICA/LOS_ANGELES','YYYY-MM-DDHH:M
-----------------------------------------------------------------
----------
20-AUG-00 05:00:00.550000000 AM AMERICA/LOS_ANGELES