Language and Territory Parameters

Previous
Previous
Next
Next

Setting different NLS parameters for local territories allows the database session to use different cultural settings. For example, you can set the euro (EUR) as the primary currency and the Japanese yen (JPY) as the secondary currency for a given database session, even when the territory is defined as AMERICA.

This section contains information about the following parameters:

NLS_LANGUAGE Parameter

The NLS_LANGUAGE parameter can be set to any valid language name. The default is derived from the NLS_LANG setting. NLS_LANGUAGE specifies the default conventions for the following session characteristics:

Example: Setting NLS_LANGUAGE=ITALIAN and Example: Setting NLS_LANGUAGE=GERMAN show the results from setting the NLS_LANGUAGE parameter to different values. In Example: Setting NLS_LANGUAGE=ITALIAN, the ALTER SESSION statement is issued to set NLS_LANGUAGE to Italian.

Setting NLS_LANGUAGE=ITALIAN

ALTER SESSION SET NLS_LANGUAGE=Italian;

-- enter a SELECT to check the format of the output after the ALTER SESSION
SELECT last_name, hire_date, ROUND(salary/8,2) salary FROM employees 
  WHERE employee_id IN (111, 112, 113);

The output from the example should be similar to the following:


LAST_NAME                 HIRE_DATE     SALARY
------------------------- --------- ----------
Sciarra                   30-SET-97      962.5
Urman                     07-MAR-98        975
Popp                      07-DIC-99      862.5

Note that the abbreviations for month names are in Italian.

In Example: Setting NLS_LANGUAGE=GERMAN, the ALTER SESSION statement is issued to change the language to German.

Setting NLS_LANGUAGE=GERMAN

ALTER SESSION SET NLS_LANGUAGE=German;

SELECT last_name, hire_date, ROUND(salary/8,2) salary FROM employees
    WHERE employee_id IN (111, 112, 113);

The output from the example should be similar to the following:


LAST_NAME                 HIRE_DATE     SALARY
------------------------- --------- ----------
Sciarra                   30-SEP-97      962.5
Urman                     07-MRZ-98        975
Popp                      07-DEZ-99      862.5

Note that the abbreviations for the month names are now in German.


See Also:


NLS_TERRITORY Parameter

The NLS_TERRITORY parameter can be set to any valid territory name. The default is derived from the NLS_LANG setting. NLS_TERRITORY specifies the conventions for the following default date and numeric formatting characteristics:

The territory can be modified dynamically during the session by specifying the new NLS_TERRITORY value in an ALTER SESSION statement. For example, to change the territory to France during a session, issue the following ALTER SESSION statement:

ALTER SESSION SET NLS_TERRITORY = France;

Modifying the NLS_TERRITORY parameter resets all derived NLS session parameters to default values for the new territory. Example: Setting NLS_LANGUAGE=AMERICAN, NLS_TERRITORY=AMERICA and Example: Setting NLS_LANGUAGE=AMERICAN and NLS_TERRITORY=GERMANY show the results from different settings of NLS_TERRITORY and NLS_LANGUAGE.

Setting NLS_LANGUAGE=AMERICAN, NLS_TERRITORY=AMERICA

-- set NLS_LANAGUAGE and NLS_TERRITORY
ALTER SESSION SET NLS_LANGUAGE = American NLS_TERRITORY = America;

-- enter the following SELECT to view the format of the output for currency
SELECT TO_CHAR(salary,'L99G999D99') salary FROM employees
     WHERE employee_id IN (100, 101, 102);

When NLS_TERRITORY is set to AMERICA and NLS_LANGUAGE is set to AMERICAN, the results should be similar to the following:


SALARY
--------------------
          $24,000.00
          $17,000.00
          $17,000.00

In Example: Setting NLS_LANGUAGE=AMERICAN and NLS_TERRITORY=GERMANY, an ALTER SESSION statement is issued to change the territory to Germany.

Setting NLS_LANGUAGE=AMERICAN and NLS_TERRITORY=GERMANY

-- set NLS_TERRITORY to Germany for this session
ALTER SESSION SET NLS_TERRITORY = Germany;

SELECT TO_CHAR(salary,'L99G999D99') salary FROM employees
    WHERE employee_id IN (100, 101, 102);

The output from the example should be similar to the following:


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

Note that the currency symbol changed from dollars ($) to euros (). The numbers have not changed because the underlying data is the same.


See Also: