learn-oracle
free Oracle DBA tutorial Oracle Jobs
Ask A Question
SQL Statement Tuning
Backup and Recovery Concepts
Oracle 11g New Features
Oracle E Suite & Others
Oracle Data Guard
Oracle DBA FAQ





Monetary, Lingusitic Sorting and Character Parameters


Previous Chapter | Next Chapter

Oracle allows you to control how currency and financial symbols appear.

Currency Formats
Many different currency formats are used throughout the world.

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

You can alter the default value of NLS_CURRENCY 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_CURRENCY statement.

NLS_ISO_CURRENCY
Parameter type: String
Parameter scope: Initialization Parameter, Environment Variable, and ALTER SESSION
Default value: Derived from NLS_TERRITORY
Range of values: Any valid territory name
This parameter specifies the character string returned by the number format mask C, the ISO currency symbol, overriding the value defined implicitly by NLS_TERRITORY.

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

You can alter the default value of NLS_ISO_CURRENCY 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_ISO_CURRENCY statement.

NLS_DUAL_CURRENCY
Parameter type: String
Parameter scope: Initialization Parameter, Environment Variable, and ALTER SESSION
Default value: Default dual currency symbol for a particular territory
Range of values: Any valid name

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
Parameter type: String
Parameter scope: Environment Variable
Default value: Derived from NLS_TERRITORY
Range of values: Any valid name

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
Parameter type: String
Parameter scope: Environment Variable
Default value: Derived from NLS_TERRITORY
Range of values: Any string, maximum of 9 bytes (not including null)

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
Parameter type: String
Parameter scope: Environment Variable
Default value: Derived from NLS_TERRITORY
Range of values: Any string, maximum of 9 bytes (not including null)

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 allows you to choose how data is sorted through the use of linguistic 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
Parameter type: String
Parameter scope: Initialization Parameter, Environment Variable, and ALTER SESSION
Default value: Default character sort sequence for a particular language
Range of values: BINARY or any valid linguistic definition name

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
Parameter type: String
Parameter scope: Initialization Parameter, Environment Variable and ALTER SESSION
Default value: Binary
Range of values: BINARY or ANSI

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
Parameter type: String
Parameter scope: Environment Variable
Default value: Derived from NLS_TERRITORY
Range of values: Any valid character
NLS_LIST_SEPARATOR specifies the character to use to separate values in a list of values.

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

You can specify the character set used for the client.

NLS_NCHAR_CONV_EXCP
Parameter type: String
Parameter scope: Environment Variable, ALTER SYSTEM, ALTER SESSION
Default value: FALSE
Range of values: TRUE, FALSE

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

Previous Chapter | Next Chapter

More Tutorials on Oracle dba ...

Source :Oracle Documentation

Liked it ? Want to share it ? Social Bookmarking

Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information




Want to share or request Oracle Tutorial articles to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info