NLS Parameters

Parameter	Description
NLS_CALENDAR	Calendar system
NLS_CURRENCY	Local currency symbol
NLS_DATE_FORMAT	Default date format
NLS_DATE_LANGUAGE	Default language for dates
NLS_ISO_CURRENCY	ISO international currency symbol
NLS_LANGUAGE	Default language
NLS_NUMERIC_CHARACTERS	Decimal character and group separator
NLS_SORT	Character sort sequence
NLS_SPECIAL_CHARS
NLS_TERRITORY	Default territory

For a complete description of ALTER SESSION, see Oracle7 Server SQL Reference.

NLS_CALENDAR

NLS_CALENDAR can have one of the following values:

• Arabic Hijrah

• Gregorian

• Japanese Imperial

• Persian

• ROC Official

• Thai Buddha

SELECT SYSDATE FROM DUAL;

SYSDATE

--------

07-02-17 

NLS_CURRENCY

NLS_CURRENCY = "Dfl "

In this case, the query

SELECT TO_CHAR(TOTAL, 'L099G999D99') "TOTAL"

   FROM ORDERS WHERE CUSTNO = 586

would return

TOTAL

-------------

Dfl 12.673,49

You can alter the default value of NLS_CURRENCY by changing its value in the initialization file and then restarting the instance, and you can alter its value during a session using an ALTER SESSION SET NLS_CURRENCY command.

For a complete description of ALTER SESSION, see Oracle7 Server SQL Reference.

NLS_DATE_FORMAT

NLS_DATE_FORMAT = "MM/DD/YYYY"

As another example, to set the default date format to display Roman numerals for months, you would include the following line in your initialization file:

NLS_DATE_FORMAT = "DD RM YY"

With such a default date format, the following SELECT statement would return the month using Roman numerals (assuming today's date is February 13, 1991):

SELECT TO_CHAR(SYSDATE) CURRDATE

   FROM DUAL;

CURRDATE

---------

13 II 91

The value of this parameter is stored in the tokenized internal date format. Each format element occupies two bytes, and each string occupies the number of bytes in the string plus a terminator byte. Also, the entire format mask has a two-byte terminator. For example, "MM/DD/YY" occupies 12 bytes internally because there are three format elements, two one-byte strings (the two slashes), and the two-byte terminator for the format mask. The tokenized format for the value of this parameter cannot exceed 24 bytes.

Note: The applications you design may need to allow for a variable-length default date format. Also, the parameter value must be surrounded by double quotes: single quotes are interpreted as part of the format mask.

You can alter the default value of NLS_DATE_FORMAT by changing its value in the initialization file and then restarting the instance, and you can alter the value during a session using an ALTER SESSION SET NLS_DATE_FORMAT command.

For a complete description of ALTER SESSION, see Oracle7 Server SQL Reference.

NLS_DATE_ LANGUAGE

NLS_DATE_LANGUAGE = FRENCH

In this case, the query

SELECT TO_CHAR(SYSDATE, 'Day:Dd Month yyyy')

   FROM DUAL;

would return

Mercredi:13 Février 1991

Month and day name abbreviations are also in the language specified, for example:

Me:13 Fév 1991

The default date format also uses the language-specific month name abbreviations. For example, if the default date format is DD-MON-YYYY, the above date would be inserted using:

INSERT INTO tablename VALUES ('13-Fév-1991');

The abbreviations for AM, PM, AD, and BC are also returned in the language specified by NLS_DATE_LANGUAGE. Note that numbers spelled using the TO_CHAR function always use English spellings; for example:

SELECT TO_CHAR(TO_DATE('27-Fév-91'),'Day: ddspth Month')

   FROM DUAL;

would return:

Mercredi: twenty-seventh Février

You can alter the default value of NLS_DATE_LANGUAGE by changing its value in the initialization file and then restarting the instance, and you can alter the value during a session using an ALTER SESSION SET NLS_DATE_LANGUAGE command.

For a complete description of ALTER SESSION, see Oracle7 Server SQL Reference.

NLS_ISO_CURRENCY

Local currency symbols can be ambiguous; for example, a dollar sign ($) can refer to US dollars or Australian dollars. ISO Specification 4217 1987-07-15 defines unique "international" currency symbols for the currencies of specific territories (or countries).

For example, the ISO currency symbol for the US Dollar is USD, for the Australian Dollar AUD. To specify the ISO currency symbol, the corresponding territory name is used.

NLS_ISO_CURRENCY has the same syntax as the NLS_TERRITORY parameter, and all supported territories are valid values. For example, to specify the ISO currency symbol for France, the parameter should be set as follows:

NLS_ISO_CURRENCY = FRANCE

In this case, the query

SELECT TO_CHAR(TOTAL, 'C099G999D99') "TOTAL"

   FROM ORDERS WHERE CUSTNO = 586

would return

TOTAL

-------------

 FRF12.673,49

You can alter the default value of NLS_ISO_CURRENCY by changing its value in the initialization file and then restarting the instance, and you can alter its value during a session using an ALTER SESSION SET NLS_ISO_CURRENCY command.

For a complete description of ALTER SESSION, see Oracle7 Server SQL Reference.

NLS_NUMERIC_ CHARACTERS

NLS_NUMERIC_CHARACTERS = ",."

Both characters are single byte and must be different. Either can be a space.

Note: When the decimal character is not a period (.) or when a group separator is used, numbers appearing in SQL statements must be enclosed in quotes. For example:

        INSERT INTO SIZES (ITEMID, WIDTH, QUANTITY)

          VALUES (618, '45,5', TO_NUMBER('1.234','9G999'));

You can alter the default value of NLS_NUMERIC_CHARACTERS by changing its value in the initialization file and then restarting the instance, and you can alter its value during a session using an ALTER SESSION SET NLS_DATE_LANGUAGE command.

For a complete description of ALTER SESSION, see Oracle7 Server SQL Reference.

NLS_SORT

The syntax of NLS_SORT is:

NLS_SORT = { BINARY | name }

BINARY specifies a binary sort and name specifies a particular linguistic sort sequence. For example, to specify the linguistic sort sequence called German, the parameter should be set as follows:

NLS_SORT = German

The name given to a linguistic sort sequence has no direct connection to language names. Usually, however, each supported language will have an appropriate linguistic sort sequence defined that uses the same name.

Note: Setting the NLS_SORT initialization parameter to BINARY causes a sort to use a full table scan, regardless of the path the optimizer chooses.

You can alter the default value of NLS_SORT by changing its value in the initialization file and then restarting the instance, and you can alter its value during a session using an ALTER SESSION SET NLS_SORT command.

For a complete description of ALTER SESSION, see Oracle7 Server SQL Reference.

A complete list of linguistic definitions is provided in the "Linguistic Definitions" table .