Monetary, Lingusitic Sorting and Character Parameters

NLS_CURRENCY

Parameter type: String
Parameter scope: Initialization Parameter, Environment Variable, and ,ALTER SESSION
Default value: Default local currency symbol for a particular territory
Range of values: Any valid currency symbol string
This parameter specifies the character string returned by the number format mask L, the local currency symbol, overriding that defined implicitly by NLS_TERRITORY. For example, to set the local currency symbol to "Dfl " (including a space), the parameter should be set as follows:

NLS_CURRENCY = "Dfl "

In this case, the query

SELECT TO_CHAR(TOTAL, 'L099G999D99') "TOTAL" FROM ORDERS WHERE CUSTNO = 586;

returns

TOTAL
-------------
Dfl 12.673,49

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, it is 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;

returns

TOTAL
-------------
FRF12.673,49

NLS_DUAL_CURRENCY

You can use this parameter to override the default dual currency symbol defined in the territory. When starting a new session without setting NLS_DUAL_CURRENCY, you will use the default dual currency symbol defined in the territory of your current language environment. When you set NLS_DUAL_CURRENCY, you will start up a session with its value as the dual currency symbol.

NLS_MONETARY_CHARACTERS

NLS_MONETARY_CHARACTERS specifies the characters that indicate monetary units, such as the dollar sign ($) for U.S. dollars, and the cent symbol (¢) for cents.

The two characters specified must be single-byte and cannot be the same as each other. They also cannot be any numeric character or any of the following characters: plus (+), hyphen (-), less than sign (<), greater than sign (>).

NLS_CREDIT

NLS_CREDIT sets the symbol that displays a credit in financial reports. The default value of this parameter is determined by NLS_TERRITORY.

This parameter can be specified only in the client environment. It can be retrieved through the OCIGetNlsInfo function.

NLS_DEBIT

NLS_DEBIT sets the symbol that displays a debit in financial reports. The default value of this parameter is determined by NLS_TERRITORY.

This parameter can be specified only in the client environment. It can be retrieved through the OCIGetNlsInfo function.

Linguistic Sorting Parameters

Oracle provides many different types of sorts, but achieving a linguistically correct sort frequently harms performance. This is a trade-off the database administrator needs to make on a case-by-case basis. A typical case would be when sorting Spanish. In traditional Spanish, ch and ll are distinct characters, which means that the correct order would be: cerveza, colorado, cheremoya, lago, luna, llama.

NLS_SORT

This parameter specifies the type of sort for character data, overriding that defined implicitly by NLS_LANGUAGE.

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 has an appropriate linguistic sort sequence defined that uses the same name.

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

NLS_COMP

You can use this parameter to avoid the cumbersome process of using NLS_SORT in SQL statements. Normally, comparison in the WHERE clause and in PL/SQL blocks is binary. To use linguistic comparison, the NLSSORT function must be used. Sometimes this can be tedious, especially when the linguistic sort needed has already been specified in the NLS_SORT session parameter. You can use NLS_COMP in such cases to indicate that the comparisons must be linguistic according to the NLS_SORT session parameter. This is done by altering the session:

ALTER SESSION SET NLS_COMP = ANSI;

To specify that comparison in the WHERE clause is always binary, issue the following statement:

ALTER SESSION SET NLS_COMP = BINARY;

As a final note, when NLS_COMP is set to ANSI, a linguistic index improves the performance of the linguistic comparison.

To enable a linguistic index, use the syntax:

CREATE INDEX i ON t(NLSSORT(col, 'NLS_SORT=FRENCH'));

NLS_LIST_SEPARATOR

The character specified must be single-byte and cannot be the same as either the numeric or monetary decimal character, any numeric character, or any of the following characters: plus (+), hyphen (-), less than sign (<), greater than sign (>), period (.).

Character Set Parameter

NLS_NCHAR_CONV_EXCP

NLS_NCHAR_CONV_EXCP determines whether data loss during an implicit or explicit character type conversion will report an error.