5 Replies Latest reply: Dec 28, 2012 6:13 AM by Roger25 RSS

    decimal characters

    Roger25
      Hi,
      We have many varchar2 columns in the database which can contain, for example '24.00' (here "." is the decimal separator), but when i run the following query on my machine i got that error
      SQL> select to_number('24.99') from dual;
       
      select to_number('24.99') from dual
       
      ORA-01722: invalid number
       
      SQL> 
      However, for example on other machines, it executes successfully. If i run select to_number('24*,*99') from dual , it executes successfully.
      I know this can be avoided by using a mask in to_number, but i don't want this.
      I tried to set the "." as the decimal separator from Control Panel -> Regional and language settings, but with no effect.

      The db version is Oracle Database 11gR2 (11.2.0.2).

      Thanks

      Edited by: Roger22 on 28.12.2012 13:53
        • 1. Re: decimal characters
          Nadeem M
          This is certainly related to your environment.

          What is the Windows version you are using ?
          Are you using any specific regional setting apart from default on ? If so what is that ?
          Does the issue persist when you use bot sqlplus & sqlplusw ?
          Start -> Run > sqlplus
          Start -> Run > sqlplusw
          • 2. Re: decimal characters
            Roger25
            Windows XP SP3.
            Yes, same from sqlplus & sqlplusw.

            I've had at "Regional options" tab, the Romanian language selected ("Select an item to match its preferences, or click Customize..."), and now i changed to English (United States), and now i can see, the Decimal separator is "." (before was ","). But does this change require a computer restart?
            • 3. Re: decimal characters
              Girish Sharma
              This is due to 'NLS_NUMERIC_CHARACTERS'. You can check by below query :

              select value from nls_session_parameters
              where parameter = 'NLS_NUMERIC_CHARACTERS'

              So, in this your case try to run by :
              SQL> select to_number('24.99','99D99','nls_numeric_characters=.,') VALUE FROM DUAL;
              
                   VALUE
              ----------
                   24.99
              
              SQL>
              http://stackoverflow.com/questions/1804448/oracles-to-number-whats-wrong

              Regards
              Girish Sharma
              • 4. Re: decimal characters
                Roger25
                SQL> select value from nls_session_parameters
                  2  where parameter = 'NLS_NUMERIC_CHARACTERS'
                  3  ;
                 
                VALUE
                ----------------------------------------
                ,.
                 
                SQL> 
                And how should i change this setting, to use only ".", even if the preference is to use 'Romanian' language?

                Edited by: Roger22 on 28.12.2012 14:11
                • 5. Re: decimal characters
                  Girish Sharma
                  See this demo in detail :
                  Connected to:
                  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                  With the Partitioning, OLAP, Data Mining and Real Application Testing options
                  
                  SQL> select value from nls_session_parameters
                    2  where parameter = 'NLS_NUMERIC_CHARACTERS'
                    3  /
                  
                  VALUE
                  ----------------------------------------
                  .,
                  
                  SQL> select to_number('24.99') from dual;
                  
                  TO_NUMBER('24.99')
                  ------------------
                               24.99
                  
                  I did not not got any error, because my  'NLS_NUMERIC_CHARACTERS' are .,  Ok, what if I change it to your environment :
                  
                  SQL> alter session set NLS_NUMERIC_CHARACTERS=',.';
                  
                  Session altered.
                  
                  SQL> select to_number('24.99') from dual;
                  select to_number('24.99') from dual
                                   *
                  ERROR at line 1:
                  ORA-01722: invalid number
                  
                  This is your error position in the question.
                  
                  SQL> select to_number('24.99','99D99','nls_numeric_characters=.,') VALUE FROM DUAL;
                  
                       VALUE
                  ----------
                       24,99
                  
                  and this is the solution.
                  
                  SQL> alter session set NLS_NUMERIC_CHARACTERS='.,';
                  
                  Session altered.
                  
                  SQL> select to_number('24.99') from dual;
                  
                  TO_NUMBER('24.99')
                  ------------------
                               24.99
                  
                  SQL>
                  If you wish that it should be ., for ever, you can say :

                  alter system set NLS_NUMERIC_CHARACTERS='.,' scope=spfile; and restart the database. (when ever you can take down time of the database, till then you can use alter session.. as per above command)

                  Regards
                  Girish Sharma