Language and Territory 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:

• Language for server messages

• Language for day and month names and their abbreviations (specified in the SQL functions TO_CHAR and TO_DATE)

• Symbols for equivalents of AM, PM, AD, and BC

• Default sorting sequence for character data when the ORDER BY clause is specified (The GROUP BY clause uses a binary sort order unless ORDER BY is specified)

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.

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:

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.

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:

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

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:

• Date format

• Decimal character and group separator

• Local currency symbol

• ISO currency symbol

• Dual currency symbol

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.

-- 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:

In Example: Setting NLS_LANGUAGE=AMERICAN and NLS_TERRITORY=GERMANY, an ALTER SESSION statement is issued to change the territory to 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:

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