What happens when a NLS patch is applied

Simple question:
  What happens when a NLS patch is applied to the Oracle server ? Do the objects get replaced by their localized parts or are there duplicates created? So one for say US English and one for the local language?
LVL 1
pvzwedenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Helena Markováprogrammer-analystCommented:
What do you mean by "NLS patch" ?
The database NLS settings are set when the database is created - see C) The Database Parameters.

Note:241047.1 - The Priority of NLS Parameters Explained
This note explains the order in which NLS parameters are taken into account in the database client/server model.(This does NOT cover Thin JDBC connections)

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';

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=<Language>_<Territory>.<clients characterset>

for example:

NLS_LANG=DUTCH_BELGIUM.WE8MSWIN1252

3) If NLS_LANG is specified with only the <Territory> part then AMERICAN is used as default <Language>.

So if you set NLS_LANG=_BELGIUM.WE8PC850 then you get this:

PARAMETER VALUE
------------------------------ --------------

NLS_LANGUAGE AMERICAN
NLS_TERRITORY BELGIUM
NLS_CURRENCY <euro sign here>
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 <Language> part then the <Territory> defaults to a setting based on <Language>.

So if you set NLS_LANG=ITALIAN_.WE8PC850 then you get this:

PARAMETER VALUE
------------------------------ --------------

NLS_LANGUAGE ITALIAN
NLS_TERRITORY ITALY
NLS_CURRENCY <euro sign here>
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 <Language>_<Territory> part then the <Language>_<Territory> part defaults to AMERICAN_AMERICA.

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 <Language>_<Territory> part.
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 <Language>_<Territory>.US7ASCII and the values for the <Language>_<Territory> part used are the ones found in 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 <clients characterset> part for the NLS_LANG like shown in point 5)

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 <clients characterset> part of NLS_LANG is *NOT* shown in any system table or view.
* 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.
* 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.
* NLS_LENGTH_SEMANTICS *cannot* be set as enviroment variable. It is however possible to do a ALTER SESSION.
* 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 <clients characterset> or NLS_LANG in the init.ora 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=.<clients characterset>
* ALTER SYSTEM SET NLS_LENGTH_SEMANTICS does not change the SESSION parameters (who take precedence) due to bug number 1488174, however it can be set in the init.ora -> 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.
* The NLS_LANGUAGE in the instance parameters also declares the language for the server error messages in alert.log and in trace files, see also [NOTE:132090.1].

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 <clients characterset> or NLS_LANG in the init.ora 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.

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 <Language>_<territory> 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.

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...

Also here are interesting informations (from MetaLink):

1. Why is my setting of NLS_DATE_FORMAT in the init.ora not used?

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

This is fully explained in:

<Note.241047.1> The Priority of NLS Parameters Explained.

If you have NLS_LANG set on the client then as a result of that you will also have a setting for NLS_DATE_FORMAT. Even if it's not set explicitly on the client, it will have a implicit value based on the default derived from the territory part of the NLS_LANG setting. That means that whatever setting you have in the init.ora gets "overwritten"
(following rule '2' from above) by this setting from the client.
See the following question on how to work around this.

2. How should the default date format be changed or set?

You can set the parameter NLS_DATE_FORMAT in the init.ora, but as we've seen in "question 1" above, if NLS_LANG is set in the client OS environment that has no effect because the "instance" setting in the init.ora is overwritten by the session setting coming from the environment variable. Therefore, if NLS_LANG is set and you want a different NLS_DATE_FORMAT than is the default, you must also set NLS_DATE_FORMAT in the client environment to override the default that is taken from the NLS_LANG.

This explained further in:

<Note.241047.1> The Priority of NLS Parameters Explained.
(btw NLS_DATE_FORMAT set as a parameter in the OS environment will not take effect unless NLS_LANG is also set.)

You can see that there are no duplicates.
I hope this will help you.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pvzwedenAuthor Commented:
it helps in clearing up my thoughts on NLS :) thanks.

I now discovered i'm looking at a language tabel (bne_param_lists_tl) which should contain some "duplicates". Some info is duplicated (like name etc) but some other things are localized.

One of the oracle packages web adi uses can;t handle this fact which turns out to be a nice problem.

Thanks for this much info on NLS.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java App Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.