9 Replies Latest reply: May 14, 2009 7:53 AM by 417843 RSS

    Change NLS_NUMERIC_CHARACTERS from ., to ,.

    417843
      Hello,

      I want to change NLS_NUMERIC_CHARACTERS value from *.,* to *,.*. I have followed below steps.

      1. select * from nls_database_parameters where PARAMETER='NLS_NUMERIC_CHARACTERS';

      PARAMETER VALUE
      --------------------------------- --------------------
      NLS_NUMERIC_CHARACTERS .,

      1. alter system set nls_numeric_characters =',.' scope=spfile
      2. Restart Database
      3. select * from nls_database_parameters where PARAMETER='NLS_NUMERIC_CHARACTERS';

      PARAMETER VALUE
      --------------------------------- --------------------
      NLS_NUMERIC_CHARACTERS .,


      Can someone please help to understand why NLS_NUMERIC_CHARACTERS is not changed to updated value. In SPFile I can see *.nls_numeric_characters=',.'

      Thanks,

      Kishor

      Edited by: Kishor Bhede on May 14, 2009 4:01 PM
        • 1. Re: Change NLS_NUMERIC_CHARACTERS from ., to ,.
          678145
          This parameter is for alter session, not alter system (false for all versions)
          Check this link about parameter:
          http://www.orafaq.com/parms/parm1322.htm

          If You need to change, then do alter session and then needed activities.
          • 2. Re: Change NLS_NUMERIC_CHARACTERS from ., to ,.
            orafad
            I don't understand the question.

            You alter instance parameter and then look at database parameters for verification?

            Try looking at NLS_INSTANCE_PARAMETERS values instead.

            If you really need to change the parameter at the database level, you probably need to create a new db. You might want to review the NLS Territory setting as well.

            Edited by: orafad on May 14, 2009 12:40 PM
            • 3. Re: Change NLS_NUMERIC_CHARACTERS from ., to ,.
              orafad
              Do you interpret too much in that, perhaps?
              SQL> alter system set open_cursors=400;
              
              System altered.
              
              SQL> alter system set nls_numeric_characters='.';
              alter system set nls_numeric_characters='.'
                               *
              ERROR at line 1:
              ORA-02096: specified initialization parameter is not modifiable with this option
              
              
              SQL> alter system set nls_numeric_characters='.' **scope=spfile**;
              
              System altered.
              
              
              
              SQL> alter system reset nls_numeric_characters scope=spfile sid='*';
              
              System altered.
              Reference guide description of Parameter descriptions:
              Modifiable
              Specifies whether the parameter can be changed for the current session (by an ALTER SESSION statement) or for all sessions in the current instance (by an ALTER SYSTEM statement):
              . ALTER SESSION overrides the instance-wide setting of the parameter for the current session only. You can restore the instance-wide setting for that session only by issuing another ALTER SESSION statement.
              . ALTER SYSTEM can be used to change the value in the server parameter file (SPFILE) of any initialization parameter. Such a change takes effect only in subsequent instances. The parameter descriptions indicate only those parameters that can be modified for the current instance.

              Edited by: orafad on May 14, 2009 1:36 PM
              • 4. Re: Change NLS_NUMERIC_CHARACTERS from ., to ,.
                417843
                Does it mean, there is no way we can modify existing database with this setting and we need to change it while creating Database

                Thanks

                - Kishor
                • 5. Re: Change NLS_NUMERIC_CHARACTERS from ., to ,.
                  orafad
                  Well, look at it this way: the docs say "NLS_DATABASE_PARAMETERS lists *permanent* NLS parameters of the database." (The view gives you just that, a view of the parameter settings)

                  Do you have a real need to modify the settings - e.g. do you have a problem with check constraints?
                  • 6. Re: Change NLS_NUMERIC_CHARACTERS from ., to ,.
                    19426
                    Can someone please help to understand why NLS_NUMERIC_CHARACTERS is not changed to updated value. In SPFile I can see *.nls_numeric_characters=',.'
                    Because the territory part of NLS_LANG overwrites it. What's your NLS_LANG setting?

                    Werner
                    • 7. Re: Change NLS_NUMERIC_CHARACTERS from ., to ,.
                      417843
                      1. From nls_instance_parameters I can see NLS_NUMERIC_CHARACTERS value is changed to *,.*, which is OK for me.
                      2. My NLS_LANG is AMERICAN_AMERICA.WE8MSWIN1252

                      I think I need to change NLS_LANG to GERMAN_GERMANY.WE8MSWIN1252, is that correct?

                      Thanks

                      Kishor
                      • 8. Re: Change NLS_NUMERIC_CHARACTERS from ., to ,.
                        19426
                        You don't need necessarily to change the language part, but territory GERMANY is correct. But keep in mind you also change other properties, refer to

                        http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang%20faq.htm

                        As an alternative define NLS_NUMERIC_CHARACTERS explicitly as environment variable or - even simpler - use 'ALTER SESSION ...' to change the value for the actual session.

                        Werner
                        • 9. Re: Change NLS_NUMERIC_CHARACTERS from ., to ,.
                          417843
                          Regarding 'ALTER SESSION ...'; I can not use it since we are trying to do some operation from Application.

                          Defining parameter in environment variable looks OK to me.

                          I got what I was looking for.

                          Thanks for the help.

                          Kishor