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.
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 in addition to the local currency symbol (which is identified by NLS_CURRENCY). It is used primarily to identify the Euro 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.
NLS_NUMERIC_CHARACTERS	The decimal marker and thousands group marker for the session.
NLS_SORT	the sequence of character values used when sorting or comparing text.
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.

Notes

Date Formats

The calendar system affects the input format for the TO_DATE function and the output produced by the TO_CHAR function.

Default Value of NLS_CURRENCY

The default value of NLS_CURRENCY controlled by the value of the NLS_TERRITORY option. Resetting NLS_TERRITORY restores NLS_CURRENCY to its default setting

Output Formats

The TO_CHAR and TO_NUMBER functions use the value of NLS_CURRENCY as their default currency symbol. Both functions can accept an optional NLS_CURRENCY argument that overrides the NLS_CURRENCY option.

Date Formats

NLS_DATE_LANGUAGE specifies the language to use for the spelling of day and month names and date abbreviations (a.m., p.m., AD, BC) returned by the TO_DATE function and the output produced by the TO_CHAR function.

Default Value of NLS_DUAL_CURENCH

The default value is the dual currency symbol defined in the territory of your current language environment which is controlled by the value of the NLS_TERRITORY option. Resetting NLS_TERRITORY restores NLS_DUAL_CURRENCY to its default setting.

Supported Symbols

When you want to identify the Euro symbol as the value of NLS_DUAL_CURRENCY, the instance character set must support that symbol.

Number Formats

The value of NLS_DUAL_CURRENCY takes the place of the letter U in a number format model. See the TO_NUMBER function for a description of number format elements.

Default Value of NLS_ISO_CURRENCY

The NLS_TERRITORY option sets the default value for NLS_ISO_CURRENCY. Resetting NLS_TERRITORY restores NLS_ISO_CURRENCY to its default value

ISO Currency Symbols

Local currency symbols can be ambiguous. For example, a dollar sign ($) can indicate Canadian dollars, U.S. dollars, or Australian dollars. ISO 4217 defines a unique three-character code for each currency, composed of a two-character country code and a one-character currency designator. For example, the code for Canadian dollars is CAD (CA for Canada, and D for dollars). Similarly, the code for U.S. dollars is USD, and for Australian dollars in AUD.

Output Format

The TO_CHAR and TO_NUMBER functions use the value of NLS_ISO_CURRENCY for the C number format element. These functions can accept an optional NLS_ISO_CURRENCY argument that overrides the NLS_ISO_CURRENCY option.

Default Values

The NLS_LANGUAGE setting controls the default values of the NLS_DATE_LANGUAGE and NLS_SORT parameters, as well as the default values for messages, day and month names, symbols for AD, BC, a.m., and p.m., and the default sorting mechanism. Some of these default values can be overridden individually by other OLAP DML options. See "Locale Settings".

Interaction with NLS_LANG

The value of NLS_LANGUAGE sets the language component of the read-only NLS_LANG option.

Locale Settings

NLS_LANGUAGE sets the default values for the NLS_DATE_LANGUAGE and NLS_SORT options. It also affects the results of the TO_CHAR function.

YESSPELL and NOSPELL

The values of the read-only YESSPELL and NOSPELL options reflect the NLS_LANGUAGE setting.

Date Formats

The value of NLS_LANGUAGE controls whether the AD and BC era designators use periods after the letters. You can change this element of the date format by using the TO_CHAR function.

Default Value of NLS_NUMERIC_CHARACTERS

The appropriate decimal and thousands markers vary from one geographic area to the next. Thus, the default value of NLS_NUMERIC_CHARACTERS is determined by the value of the NLS_TERRITORY option. Resetting NLS_TERRITORY restores NLS_NUMERIC_CHARACTERS to its default value

Format of Numeric Input

NLS_NUMERIC_CHARACTERS affects only the display of numeric data. When supplying values for input to an OLAP DML statement, do not use a thousands group marker, and use a period (.) for the decimal marker. To use a different markers for input data, enclose the value in single quotes and use the TO_NUMBER function to convert the value from text to a valid number.

Effect on Other Options

A change to NLS_NUMERIC_CHARACTERS causes an equivalent change in either the THOUSANDSCHAR option, the DECIMALCHAR option, or both.

Output Format Overrides

The TO_CHAR, and TO_NUMBER functions have an optional NLS_NUMERIC_CHARACTERS argument that overrides the NLS_NUMERIC_CHARACTERS option.

Performance

Linguistically correct sorting is slower than binary sorting.

Asian Languages

East Asian languages are typically sorted by their binary values, since linguistically appropriate sorting is often too difficult and complex to be feasible.

Comparison Operators

The value of NLS_SORT affects the GT, GE, LT, and LE operators.

Sorting Statements

The value of NLS_SORT affects the SORT command and the SORTLINES function.

Default Values

The setting of NLS_TERRITORY controls the default values of the thousands separator, the decimal marker, the currency symbol, and the sort order. These default values can be overridden individually by other OLAP DML options. See "Locale Settings".

Interaction with NLS_LANG

The value of NLS_TERRITORY sets the territory component of the read-only NLS_LANG option.

Locale Settings

NLS_TERRITORY sets the default values for the NLS_DATE_FORMAT, NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_NUMERIC_CHARACTERS, and NLS_DUAL_CURRENCY options. Setting NLS_TERRITORY will reset these options to their default values.

Numeric Formatting

NLS_TERRITORY affects numeric formatting in the TO_CHAR function.

Date Formatting

NLS_TERRITORY affects date formatting in the TO_DATE function.

Examples

Example 18-25 Changing Calendar Systems

The following statement sets NLS_CALENDAR to the Thai Buddha calendar.

NLS_CALENDAR = 'THAI BUDDHA'

Example 18-26 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-27 Setting the Second Currency Symbol

The following statement sets NLS_DUAL_CURRENCY to the symbol for pounds sterling.

NLS_DUAL_CURRENCY = '£'

Example 18-28 Setting the International Currency Symbol

The following statement changes the ISO symbol to French francs (FRF).

NLS_ISO_CURRENCY = 'FRANCE'

Example 18-29 Checking the Current Value

A SHOW NLS_LANG statement might produce the following.

AMERICAN_AMERICA.WE8ISO8859P1

Example 18-30 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-31 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-32 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-33 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