NLS Options

Oracle bases its globalization support on the values of parameters that begin with NLS. The Oracle OLAP DML includes a number options that correspond to these parameters.

Within a session, you can dynamically modify the value of some of these NLS parameters by setting them using the OLAP DML options described in Table 18-2, "OLAP DML NLS Options" or by using the SQL statement ALTER SESSION SET option = value.

Table 18-2 OLAP DML NLS Options

Option Name	Description
NLS_CALENDAR	The calendar for the session.
NLS_CURRENCY	The local currency symbol for the `L` number format element for the session. (See the TO_NUMBER function for a description of number format elements.)
NLS_DATE_FORMAT	The default format for `DATETIME` values.
NLS_DATE_LANGUAGE	The language for days, months, and similar language-dependent `DATE` format elements.
NLS_DUAL_CURRENCY	A second currency symbol that takes the place of the letter `U` in a number format mode and is used primarily to identify the Euro symbol. (Note that when you want to identify the Euro symbol as the value of NLS_DUAL_CURRENCY, the instance character set must support that symbol.)
NLS_ISO_CURRENCY	The international currency symbol for the `C` number format element.
NLS_LANG	The current language, territory, and database character set, which are determined by session-wide globalization parameters.
NLS_LANGUAGE	The current language for the session. The setting of this option determines the value of the SESSION_NLS_LANGUAGE option.
NLS_NUMERIC_CHARACTERS	The decimal marker and thousands group marker for the session. NLS_NUMERIC_CHARACTERS affects the display of numeric data and the setting of the OLAP DML THOUSANDSCHAR option, the DECIMALCHAR option, or both.
NLS_SORT	the sequence of character values used when sorting or comparing text. The value of NLS_SORT affects the `GT`, `GE`, `LT`, and `LE` operators, SORT command, and SORTLINES.
NLS_TERRITORY	The current territory for the session.

Data Type

TEXT

Syntax

option-name = option-value

Arguments

See Oracle Database SQL Reference for more information about NLS parameters, including valid values.

Examples

Example 18-21 Changing Calendar Systems

The following statement sets NLS_CALENDAR to the Thai Buddha calendar.

NLS_CALENDAR = 'THAI BUDDHA'

Example 18-22 Setting the Language for Dates

The following statements set the language for dates to Spanish and change the default date format.

NLS_DATE_LANGUAGE = 'SPANISH'
NLS_DATE_FORMAT = 'Month DD, YYYY'

A SHOW SYSDATE statement now generates the date in Spanish.

Septiembre 08, 2000

Example 18-23 Checking the Current Value

A SHOW NLS_LANG statement might produce the following.

AMERICAN_AMERICA.WE8ISO8859P1

Example 18-24 Effects of Changing NLS_LANGUAGE

In this example, the NLS_LANG option is initially set to:

AMERICAN_AMERICA.WE8ISO8859P1

The value of YESSPELL is yes.

A change to the language setting:

NLS_LANGUAGE = 'FRENCH'

changes the value of NLS_LANG to

FRENCH_AMERICAN.WE8ISO8859P1

The value of YESSPELL is now oui.

Example 18-25 Changing the Decimal Marker to a Comma

The following statement changes the decimal marker to a comma, and the thousands marker to a space.

NLS_NUMERIC_CHARACTERS = ', '

The result of the following statement

show 1234.56

is now

1 234,56

Example 18-26 Binary and Linguistic Sorts

A dimension named words has the following values.

cerveza, Colorado, cheremoya, llama, luna, lago

This example shows the results of a binary sort.

NLS_SORT = 'BINARY'
SORT words A words
STATUS words
The current status of WORDS is:
Colorado, cerveza, cheremoya, lago, llama, luna

A Spanish language sort results in this order.

NLS_SORT = 'SPANISH'
SORT words A words
STATUS words
The current status of WORDS is:
cerveza, cheremoya, Colorado, lago, llama, luna

An extended Spanish language sort results in this order.

NLS_SORT = 'XSPANISH'
SORT words A words
STATUS words
The current status of WORDS is:
cerveza TO cheremoya, lago TO llama

Example 18-27 Effects of Changing NLS_TERRITORY

In this example, the NLS_LANG option is initially set to:

AMERICAN_AMERICA.WE8ISO8859P1

The thousands marker is a comma (,), and the decimal marker is a period (.).

SHOW TO_NUMBER('12345')
12,345.00

A change to the territory setting:

NLS_TERRITORY = 'FRANCE'

changes the value of NLS_LANG to

AMERICAN_FRANCE.WE8ISO8859P1

The thousands marker is now a period (.), and the decimal marker is a comma (,).

SHOW TO_NUMBER('12345')
12.345,00