Oracle: NLS related init parameters

NLS_COMP

NLS_COMP can be set to one of

BINARY
LINGUISTIC (Behvavior of comparison is specified with nls_sort)
ANSI (supported for backward compatibility, LINGUISTIC is preferred)

If NLS_COMP is not set in the init parameters, its value shows up as null in V$PARAMETER, V$SYSTEM_PARAMETER, V$PARAMETER2, V$SYSTEM_PARAMETER2 and NLS_INSTANCE_PARAMETERS, but behaves as though it was set to BINARY.

NLS_DATE_FORMAT

Gone here.

NLS_LANG

The value of NLS_LANG sets the language and territory of both, the session running on the Server, and the client.

NLS_LANG has three components, all of which are optional.

language	Controls the language of day and month names and error messages, and sorting.
territory	Specifies the default format for dates, numbers and monetary values.
character set	Specifies the character set with which a user works. Each language has a default character set assosiated with it (which seems to be used when character set is not explicitly specified)

These components are separated between the language and the territory with an underscore and between the territory and character set with a dot (for example FRENCH.CANADA.WE8ISO8859P1)

The value of NLS_LANG should correspond to that of the client's operating system.

TODO: Setting the environment variable NLS_LANG prior to starting SQL*Plus.

NLS_LENGTH_SEMANTICS

The value of NLS_LENGTH_SEMANTICS can be set to BYTE (default value) or CHAR.

For database objects creted in the current session, this value specifies, the default length semantics to use for

VARCHAR2 and CHAR table columns,
user-defined object attributes and
PL/SQL variables.

While the value of NLS_LENGTH_SEMANTICS affects the use of VARCHAR2 and CHAR, the NCHAR, NVARCHAR2, CLOB and NCLOB are always character-based.

NLS_NUMERIC_CHARACTERS

The value of nls_numeric_characters consists of two characters:

The first specifies the decimal separator (with which a D is replaced in to_char, see format model for numbers)
The second specifes the group separator (thousands separator, with which a G is replaced in to_char, see format model for numbers)

See also the ORA-01481: invalid number format model error message.

NLS_SORT

The value of NLS_SORT specifies the collating sequence for character value comparison. Thus, this value influences the outcome of

order by
group by
Comparison operators (such as <, >, <=, >= and even = or <>).
The in operator
like, instr
min, max, greatest, least
Execution plans where indexes are involved (compare with the nlssort() function)

The exact operators and query clauses that obey the NLS_SORT parameter depend on the value of the NLS_COMP parameter. If an operator or clause does not obey the NLS_SORT value, as determined by NLS_COMP, the collation used is BINARY.

Setting the value of nls_sort to binary causes the values to be sorted according to the byte value of the compared value.

If _CI is appended to the value of NLS_SORT, the values are compared case-insensitively (see for example selecting case insensitively in a where clause).

Appending _AI causes the values to be sorted accent-insensitively.

The value of NLS_SORT can also be set using the environment variable NLS_SORT.

The current value of NLS_SORT is returned by sys_context('userenv', 'nls_sort').

The value of NLS_SORT influences the behavior of comparison operations (for example in the where clause) if NLS_COMP is set to linguistics;

NLS_TERRITORY

The value of nls_territory determines the value of the following init parameters if they're not explicitly altered:

nls_date_format
nls_numeric_characters
nls_currency
nls_iso_currency

The value also influences the day of week (to_char(…, 'D')).