6 Replies Latest reply: Aug 24, 2011 8:32 PM by user9015969 RSS

    nls_database_parameters

    user9015969
      Hi All,

      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 !
        • 1. Re: nls_database_parameters
          Nadeem M
          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.

          http://www.oracle.com/technetwork/database/globalization/nls-lang-099431.html

          If you can let us know about your exact requirement then its easy to comment further.
          • 2. Re: nls_database_parameters
            Sergiusz Wolicki-Oracle
            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.


            -- Sergiusz
            • 3. Re: nls_database_parameters
              orafad
              user9015969 wrote:
              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" ?
              Perhaps you could start by describing the actual problem/requirement that led you to wanting to change the parameters?
              • 4. Re: nls_database_parameters
                user9015969
                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 !
                • 5. Re: nls_database_parameters
                  Sergiusz Wolicki-Oracle
                  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.

                  Example:

                  Let assume that NLS_DATABASE_PARAMETERS shows NLS_LANGUAGE=AMERICAN and thus, usually, NLS_DATE_LANGUAGE=AMERICAN. Then:

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

                  The below code will work:
                  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');
                  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.

                  Neither NLS_SESSION_PARAMETERS nor NLS_INITIALIZATION_PARAMETERS influence the above example.

                  -- Sergiusz
                  • 6. Re: nls_database_parameters
                    user9015969
                    Thanks Lot !

                    Appriciate your support !