Skip Headers
Oracle® OLAP DML Reference
10g Release 2 (10.2)

Part Number B14346-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

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