TO_NUMBER() Function In Oracle Behaves Differently

I have string variables and these variables differ in length. Let’s assume that I have the following strings:

23.2
89.78
7895.369
7.2398539
0.1

I want to convert my string variables to float. So, to do that, I call TO_NUMBER() function. The problem is when I call TO_NUMBER() function from my .NET web application instead of calling it directly within pl/sql block, it gives me an “invalid number” error.

I want to be able to call TO_NUMBER() function from both my web application and within pl/sql block at the same time. Because it is in a stored procedure. I think that is something about NLS Parameters. But I couldn’t make it work.

To sum up:

TO_NUMBER(string) syntax works fine if I call the function within PL/SQL block. But it gives me an “invalid number” error when I call it from my web application.

I want the exact value in float data type.

Answer

You might also want to call TO_NUMBER with the right NLS_NUMERIC_CHARACTER settings to avoid conflicts between your application side and your database side

23.2
89.78
7895.369
7.2398539
0.1

Then

SQL> with my_numbers as
  2  (
select 23.2        as c1 from dual union all
  3    4  select 89.78       as c1 from dual union all
select 7895.369    as c1 from dual union all
select 7.2398539   as c1 from dual union all
select 0.1         as c1 from dual
  5    6    7    8  ) select to_number ( c1 , '99999999999999D99999999999999', 'NLS_NUMERIC_CHARACTERS=''.,''' ) as result
from my_numbers ;  9

    RESULT
----------
      23.2
     89.78
  7895.369
 7.2398539
        .1

If you believe you might have no number, use @Gordon Linoff answer to look for non numeric values.

With different NLS settings

SQL> with my_numbers as
(
select 23.2        as c1 from dual union all
select 89.78       as c1 from dual union all
select 7895.369    as c1 from dual union all
select 7.2398539   as c1 from dual union all
select 0.1         as c1 from dual
) select to_number ( c1 , '99999999999999D99999999999999', 'NLS_NUMERIC_CHARACTERS='',.''' ) as result
from my_numbers ;  2    3    4    5    6    7    8    9

    RESULT
----------
       232
      8978
   7895369
  72398539
         1