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 20-2, "OLAP DML NLS Options" or by using the SQL statement ALTER SESSION SET option = value.  

Table 20-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 20-21 Changing Calendar Systems

The following statement sets NLS_CALENDAR to the Thai Buddha calendar.

NLS_CALENDAR = 'THAI BUDDHA' 

Example 20-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 20-23 Checking the Current Value

A SHOW NLS_LANG statement might produce the following.

AMERICAN_AMERICA.WE8ISO8859P1 

Example 20-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 20-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 20-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 20-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