This content has been marked as final. Show 6 replies
You can set the NLS_LANGUAGE and NLS_TERRITORY parameters to desired value in your initialization parameter file.
But note that this might not effect your clients. Client side values are usually configured with NLS_LANG environment variable. Check this link for more details.
If you can let us know about your exact requirement then its easy to comment further.
The NLS_LANGUAGE/NLS_TERRITORY values in NLS_DATABASE_PARAMETERS cannot be changed once the database has been created (they come from init.ora at the time of CREATE DATABASE). But those values are used generally only to provide NLS environment for evaluation of CHECK constraints. As you should write your constraints in an NLS-independent way anyway, the values should not matter.
NLS_SESSION_PARAMETERS is the view that shows values in effect for normal query and DML processing.
user9015969 wrote:Perhaps you could start by describing the actual problem/requirement that led you to wanting to change the parameters?
When I query with "SELECT * FROM nls_database_parameters" I get NLS_LANGUAGE as "AMERICAN" and NLS_TERRITORY as "AMERICA" .
Could you please tell me the steps to change thes as NLS_LANGUAGE = GERMAN and NLS_TERRITORY as "GERMANY" ?
Thanks for the information. My understanding is that what we have in NLS_DATABASE_PARAMETERS can be over written by NLS_INSTANCE_PARAMETERS and INSTANCE settings can be over written by client session parameters.
My issue was when I migrate one of the database ( by using DUMP ), in my new database NLS parameters used default settings so that NLS_LANGUAGE and NLS_TERRITORY has taken default "AMERICA" and "AMERICAN" values. But actually my OLD database had these values as "GERMAN" and "GERMANY".
I changed NLS_LANGUAGE and NLS_TERRITORY in instance level as I needed. I think according to the information you experts provided and information I found in the metalink ( Doc ID 241047.1 ) , I have done the correct thing. Please correct me if I am wrong.
Thanks again for your valuable information.
Simply great !
Actually, the picture looks like this: NLS_DATABASE_PARAMETERS are used to evaluate CHECK constraints. They can be overridden only by specifying the parameters in SQL functions themselves.
Let assume that NLS_DATABASE_PARAMETERS shows NLS_LANGUAGE=AMERICAN and thus, usually, NLS_DATE_LANGUAGE=AMERICAN. Then:
The above code will fail because TO_CHAR will be evaluated with NLS_DATE_LANGUAGE=AMERICAN and the 1st of December will return DEC and not DEZ.
create table TAB1 ( COL1 date, check (TO_CHAR(COL1,'MON') = 'DEZ') ); alter session set nls_language=GERMAN; insert into TAB1 values (date '2011-12-01');
The below code will work:
Here, the third parameter to TO_CHAR overrides the NLS_DATABASE_PARAMETERS. You should usually use this third parameter to make your CHECK constraint independent of the database environment.
create table TAB1 ( COL1 date, check (TO_CHAR(COL1,'MON','NLS_DATE_LANGUAGE=GERMAN') = 'DEZ') ); alter session set nls_language=GERMAN; insert into TAB1 values (date '2011-12-01');
Neither NLS_SESSION_PARAMETERS nor NLS_INITIALIZATION_PARAMETERS influence the above example.