select * from nls_session_parameters order by parameter;
NLS_CALENDAR GREGORIAN
NLS_COMP BINARY
NLS_CURRENCY SFr.
NLS_DATE_FORMAT yyyy-mm-dd hh24:mi:ss
NLS_DATE_LANGUAGE ENGLISH
NLS_DUAL_CURRENCY SF
NLS_ISO_CURRENCY SWITZERLAND
NLS_LANGUAGE ENGLISH
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .'
NLS_SORT BINARY
NLS_TERRITORY SWITZERLAND
NLS_TIMESTAMP_FORMAT yyyy-mm-dd hh24:mi:ssXff
NLS_TIMESTAMP_TZ_FORMAT yyyy-mm-dd hh24:mi:ssXff tzr
NLS_TIME_FORMAT HH24:MI:SSXFF -- Used for internal purposes only
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR -- Used for internal purposes only
utl_file assumes the files it reads or writes are to be in the character set specified with nls_characterset.
A list of valid values for nls_characterset can be obtained with
select
value,
isdeprecated
from
v$nls_valid_values
where
parameter = 'CHARACTERSET'
order by
value;
Compare with nls_nchar_characterset
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.
The purpose of NLS_LANG is to specify the character set of the client so that Oracle can correctly convert between the client's encoding (nls_lang) and the database's encoding (nls_characterset, nls_nchar_characterset).
The value of NLS_LANG sets the language and territory of both, the session running on the Server, and the client.
Do not confuse NLS_LANG with NLS_LANGUAGE (NLS_LANG, unlike NLS_LANGUAGE, is not an init parameter that can be set in init.ora or an spfile).
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
with an underscore between the language and the territory and
with a dot between the territory and character set
An example of an NLS_LANG value is FRENCH_CANADA.WE8ISO8859P1.
The language specifies the value of NLS_LANGUAGE, the value of territory the value of NLS_TERRITORY.
The value of NLS_LANG should correspond to that of the client's operating system.
Determining the value of NLS_LANG
select
any_value(client_charset) nls_lang
from
v$session_connect_info
where
sid = sys_context('userenv', 'sid');
In SQL*Plus, when running in Windows, the value of NLS_LANG can be determined with the following esoteric command (see here):
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.