Doc ID: Note:241047.1 Type: BULLETIN
Last Revision Date: 21-AUG-2007 Status: PUBLISHED
This note explains the order in which NLS parameters are taken into account
in the database client/server model using a standard client connection.
(This does NOT cover JDBC connections, please see Note 115001.1
NLS_LANG Client Settings and JDBC Drivers for more info on jdbc and NLS.)
There are 3 levels at which you can set NLS parameters: Database, Instance and
Session. If a parameter is defined at more than one level then the rules on
which one takes precedence are quite straighforward:
1. NLS database settings are overwritten by NLS instance settings
2. NLS database & NLS instance settings are overwritten by NLS session settings
In the remainder of this note we shall explain all the different settings in
detail. The categories A to C shown below indicate the order of precedence with
A being the highest and C the lowest.
For example, if you set NLS_NUMERIC_CHARACTERS in the init.ora (point B) and
in the environment (point A 6), then for a session the value defined in the
environment will take priority because point A 6 comes before point B.
A) The Session Parameters.
--------------------------
select * from NLS_SESSION_PARAMETERS;
These are the settings used for the current sql session.
These reflect (in this order):
1) The values of NLS parameters set by "alter session .... "
alter session set NLS_DATE_FORMAT = 'DD/MM/YYYY';
* this can also been done with an after logon trigger(!).
select OWNER, TRIGGER_NAME, TRIGGER_BODY from DBA_TRIGGERS where
trim(TRIGGERING_EVENT) = 'LOGON';
-> Note 251044.1 How to set a NLS session parameter at database
level for all sessions ?
2) If there are no explicit "alter sessions ..." statements done then it
reflects the setting of the corresponding NLS parameter on the client derived
from the NLS_LANG variable.
NLS_LANG consist of: NLS_LANG=
for example:
NLS_LANG=DUTCH_BELGIUM.WE8MSWIN1252
For information on how to find the NLS_LANG your sqlplus session is using
see point "4.2 How can I Check the Client's NLS_LANG Setting?" in Note 158577.1
3) If NLS_LANG is specified with only the
then AMERICAN is used as default
So if you set NLS_LANG=_BELGIUM.WE8PC850 then you get this:
PARAMETER VALUE
------------------------------ --------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY BELGIUM
NLS_CURRENCY
NLS_ISO_CURRENCY BELGIUM
....
Note the difference between NLS_LANG=_BELGIUM.WE8PC850 (correct) and
NLS_LANG=BELGIUM.WE8PC850 (incorrect), you need to set the "_" as separator.
4) If NLS_LANG is specified with only the
defaults to a setting based on
So if you set NLS_LANG=ITALIAN_.WE8PC850 then you get this:
PARAMETER VALUE
------------------------------ --------------
NLS_LANGUAGE ITALIAN
NLS_TERRITORY ITALY
NLS_CURRENCY
NLS_ISO_CURRENCY ITALY
.....
Note the difference between NLS_LANG=ITALIAN_.WE8PC850 (correct) and
NLS_LANG=ITALIAN.WE8PC850 (incorrect), you need to set the "_" as separator.
5) If NLS_LANG is specified without the
then the
So if you set NLS_LANG=.WE8PC850 then you get this:
PARAMETER VALUE
------------------------------ ----------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
....
Note the difference between NLS_LANG=.WE8PC850 (correct) and
NLS_LANG=WE8PC850 (incorrect), you need to set the "." as separator.
6) If the NLS_LANG is set (either like in point 3,4 or 5) then parameters like
NLS_SORT, NLS_DATE_FORMAT,... can be set as a "standalone" setting and will
overrule the defaults derived from NLS_LANG
So if you set NLS_LANG=AMERICAN_AMERICA.WE8PC850 and NLS_ISO_CURRENCY=FRANCE
then you get this:
PARAMETER VALUE
------------------------------ -----------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY FRANCE
...
* Make sure that you set "NLS_ISO_CURRENCY=FRANCE", NLS_ISO_CURRENCY= FRANCE
(note the space) will not give an error but the parameter is just ignored
and the default based on NLS_TERRITORY will be used.
Defaults:
---------
* If NLS_DATE_LANGUAGE or NLS_SORT are not set then they are derived from
NLS_LANGUAGE.
* If NLS_CURRENCY, NLS_DUAL_CURRENCY, NLS_ISO_CURRENCY, NLS_DATE_FORMAT,
NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, NLS_NUMERIC_CHARACTERS are
not set then they are derived from NLS_TERRITORY
7) If the NLS_LANG is not set at all, then it defaults to
NLS_INSTANCE_PARAMETERS. Parameters like NLS_SORT defined as "standalone" on
the client side are ignored.
* Oracle does NOT recommend to have the NLS_LANG UNSET, please always define
at least the proper
in point 5)
See Note 158577.1 NLS_LANG Explained (How does Client-Server Character Conversion Work?)
and Note 179133.1 The correct NLS_LANG in a Windows Environment
Note that:
* If set, client parameters (NLS_SESSION_PARAMETERS) take always precedence
above NLS_INSTANCE_PARAMETERS and NLS_DATABASE_PARAMETERS.
* This behavior can not be disabled on/from the server, so a parameter set
on the client always has precedence above an instance or database parameter.
* NLS_LANG cannot be changed by alter session, NLS_LANGUAGE and NLS_TERRITORY
can. However NLS_LANGUAGE and /or NLS_TERRITORY cannot be set as a "standalone" parameters
in the enviroment or registry on the client.
* NLS_SESSION_PARAMETERS is NOT visible for other sessions. If you need to trace
this then you have to use a logon trigger to create your own logging table
(based on session_parameters)
* The
or view. (see section 4.2 How can I Check the Client's NLS_LANG Setting? in
Note 158577.1)
* On Windows you have two possible options, normally the NLS_LANG is set in
the registry, but it can also be set in the environment, however this is not
often done and genrally not recommended to do so.
The value in the environment takes precedence over the value in
the registry and is used for ALL Oracle_Homes on the server(!) if defined as
a system environment variable.
See Note 179133.1 The correct NLS_LANG in a Windows Environment
* NLS_COMP *cannot* be set as enviroment variable (unlike documented in the
manual). All Oracle8i and Oracle9 versions use NLS_COMP from INIT.ORA or
from explicit ALTER SESSION.
Bug 2155062 NLS_COMP cannot be set in the client ENVIRONMENT
* NLS_LENGTH_SEMANTICS *cannot* be set as enviroment variable in 9i,
from 10g onwards it can be, please note that it needs to be set as UPPERCASE.
It is however possible to do a ALTER SESSION.
If not set explicit in a session it will use the NLS_INSTANCE_PARAMETER setting.
* NLS_NCHAR_CONV_EXCP *cannot* be set as enviroment variable.
It is however possible to do a ALTER SESSION.
* NLS_LANGUAGE in the session parameters also declares the language for the
client error messages, see also Note 132090.1.
* you cannot "set" a NLS parameter in an SQL script, you need to use alter
session.
B) The Instance Parameters.
---------------------------
select * from NLS_INSTANCE_PARAMETERS;
These are the settings in the init.ora of the database at the moment that
the database was started or set trough ALTER SYSTEM.
If the parameter is not explicitly set in the init.ora / defined by
ALTER SYSTEM then it's value is NOT derived from a "higher" parameter
(=we are talking about parameters like ex. NLS_SORT who
derive a default from NLS_LANGUAGE in NLS_SESSION_PARAMETERS,
this is NOT the case for NLS_INSTANCE_PARAMETERS )
Note that:
* NLS_LANG is not a init.ora parameter, NLS_LANGUAGE and NLS_TERRITORY are.
so you need to set NLS_LANGUAGE and NLS_TERRITORY separated.
* you cannot define the
the clients characterset is defined by the NLS_LANG on client side (see above).
* you cannot define the database characterset in the init.ora.
The database characterset is defined by the "Create Database" command (see point c)).
* These settings take precedence above the NLS_DATABASE_PARAMETERS.
* These values are used for the NLS_SESSION_PARAMETERS if on the client the
NLS_LANG is NOT set (we strongly advice to set the NLS_LANG).
* Oracle *strongly* recommends that you set the NLS_LANG on the client at least to
NLS_LANG=.
* ALTER SYSTEM SET NLS_LENGTH_SEMANTICS does not change the SESSION parameters
(who take precedence) due to bug number 1488174 until the database is restarted.
however it can be set in the init.ora or Spfile -> see Note 144808.1
* ALTER SYSTEM SET NLS_NCHAR_CONV_EXCP does not change the SESSION parameters
(who take precedence).. workaround: use a init.ora parameter.
C) The Database Parameters.
---------------------------
select * from NLS_DATABASE_PARAMETERS;
These are always defaulting to american america if there were no parameters
explicitly set in the init.ora during database creation time (!).
If there were parameters set in the init.ora during database creation you see
them here. There is no way to change these after the database creation.
Do NOT update systemtables!
These settings are used to give the database a default if the INSTANCE and
SESSION parameters are not set.
Note that:
* NLS_LANG is not a init.ora parameter, NLS_LANGUAGE and NLS_TERRITORY are.
So you need to set NLS_LANGUAGE and NLS_TERRITORY separatly.
* These parameters are overruled by NLS_INSTANCE_PARAMETERS and
NLS_SESSION_PARAMETERS.
* you cannot define the
the clients characterset is defined by the NLS_LANG on client side (see above).
* you cannot define the database characterset in the init.ora.
The database (national) characterset (NLS_(NCHAR)_CHARACTERSET)
is defined by the "Create Database ..." command.
* The NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET parameters cannot be
overruled by instance or session parameters.
They are defined by the value specified in "create database ..." and are not
intended to be changed afterwards dynamically.
Do NOT update systemtables to change the characterset.
This will corrupt your database and potentialy it will by impossible to open
the database again.
See Note 225912.1 Changing the Database Character Set - an Overview
If you want to change the database characaterset. Don't mess with this if you
don't know what you are doing. Log a tar if any doubt.
* Setting the NLS_LANG during the creation of the database dous not influence
the NLS_DATABASE_PARAMETERS.
* The NLS_LANG set during the database creation has NO impact on the database
(national) Characterset.
* These settings are used in evaluation of CHECK constraints if TO_CHAR/TO_DATE
without a date format is used in the CHECK condition. Writing CHECK
constraints without explicit date formats is a bad habit, you should use
explicit formats and this setting becomes irrelevant.
Additional selects:
-------------------
A) select name,value$ from sys.props$ where name like '%NLS%';
This gives the same info as NLS_DATABASE_PARAMETERS.
You should use NLS_DATABASE_PARAMETERS instead of props$.
Note the UPPERCASE '%NLS%'
B) select * from v$nls_parameters;
A view that shows the current session parameters and
the *DATABASE* characterset as seen in the NLS_DATABASE_PARAMETERS view.
C) select name,value from v$parameter where name like '%nls%';
This gives the same info as NLS_INSTANCE_PARAMETERS.
Note the LOWERCASE '%nls%'
D) select userenv ('language') from dual;
and
select sys_context('userenv','language') from dual;
Both these select statements give the session's
and the *DATABASE* character set. The database character set is not the
same as the character set of the NLS_LANG that you started this connection
with! So don't be fooled, although the output of this query looks like the
value of a NLS_LANG variable - it is NOT.
For more info on SYS_CONTEXT please see Note 120797.1
E) select userenv ('lang') from dual;
This select gives the short code that Oracle uses for the Language defined
by NLS_LANGUAGE setting for this session.
If NLS_LANGUAGE is set to French then this will return "F",
if NLS_LANGUAGE is set to English then this will return "GB"
If NLS_LANGUAGE is set to American then this will return "US", and so on...
F) show parameter NLS%
This will give the same as the NLS_INSTANCE_PARAMETERS
Remark:
-------
The
or view (it is not known in the database). If you require to know the current
setting of a clients NLS_LANG then please see section 4.2 (How can I Check the
Client's NLS_LANG Setting?) of Note 158577.1
Related Documents:
------------------
Note 179133.1 The correct NLS_LANG in a Windows Environment
Note 158577.1 NLS_LANG Explained (How does Client-Server Character Conversion Work?)
Note 227331.1 Setting NLS Parameters - Frequently Asked Questions
Note 13978.1 NLS Sort Characteristics
Note 13882.1 Linguistic Sorting of Data in Oracle7 and Oracle8
Note 30557.1 NLS_DATE_FORMAT and a Default Century
Note 225912.1 Changing the Database Character Set - an Overview
Note 132090.1 How to get messages in your own language on MS Windows platform?
Note 120797.1 How to Determine Client IP-address,Language & Territory and Username for Current Session
Note 251044.1 How to set a NLS session parameter at database level for all sessions ?
Note 144808.1 Examples and limits of BYTE and CHAR semantics usage
Bug 2155062 NLS_COMP cannot be set in the client ENVIRONMENT
For further NLS / Globalization information you may start here:
Note 267942.1 Globalization Technology (NLS) Knowledge Browser
Note 60134.1 NLS Frequently Asked Questions
No comments:
Post a Comment