ORA-01722: Invalid number for Fr Locale for Oracle 11g

0
votes

I have a view named My_View which contains one varchar column having numeric decimal data. While,select calculating avg in am getting error

ORA-01722: Invalid number for Fr Locale

Here is my oracle query which i tried but getting the error: 

select (AVG(MY_COLUMN)) 
from  MY_TABLE;

select TO_NUMBER((AVG(MY_COLUMN)), '90.9999', 'NLS_NUMERIC_CHARACTERS='',.''') 
from  MY_TABLE
GROUP BY MY_COLUMN;

How to get rid of this error?

2
databasestringoracleoracle11gaverage
:one varchar column having numeric decimal data" Presumably you know this is bad design. Anyway it sounds like you have some data into your data which contains values that cannot be cast to numbers. Perhaps you could use something like this is_number() user-defined function in a WHERE clause so your view only selects values which can be converted. (In 12c R2 Oracle provide a built-in VALIDATE_CONVERSATION() which does this.) – APC
@APC: Did you mean VALIDATE_CONVERSION? (And thanks - I'm glad I'm not the only one... :-) – Bob Jarvis - Reinstate Monica

2 Answers

3
votes

Starting with Oracle 12.2, you can use the on conversion error clause of to_number() to return a default value when the conversion fails. This is handy for your use case: you can return null on conversion error, which aggregate function avg() will happily ignore.

select avg(
    to_number(
        my_column default null on conversion error, 
        '9999d9999', 
        'nls_numeric_characters = ''.,'''
    )
) my_avg
from my_table;
2
votes

Problem seems to be in data that isn't "numeric" (why do you keep numbers in VARCHAR2 columns)? For example, it contains '123A56'. What is AVG of that value?

A simple option is to use REGEXP_LIKE and perform numeric operations only on "valid" values. For example:

SQL> with test (col) as
  2    (select '1234.56' from dual union all  -- valid
  3     select '131'     from dual union all  -- valid
  4     select 'ABC'     from dual union all  -- invalid
  5     select 'xy.23'   from dual union all  -- invalid
  6     select '.3598'   from dual union all  -- invalid
  7     select '12.34.56'from dual            -- invalid
  8    )
  9  select col,
 10         to_number(col, '9999D9999', 'nls_numeric_characters = ''.,''') col_as_num
 11  from test
 12  where regexp_like(col, '^\d+\.?\d+$');

COL      COL_AS_NUM
-------- ----------
1234.56     1234,56
131             131

SQL>

Now you can AVG such values:

SQL> with test (col) as
  2    (select '1234.56' from dual union all  -- valid
  3     select '131'     from dual union all  -- valid
  4     select 'ABC'     from dual union all  -- invalid
  5     select 'xy.23'   from dual union all  -- invalid
  6     select '.3598'   from dual union all  -- invalid
  7     select '12.34.56'from dual            -- invalid
  8    )
  9  select avg(to_number(col, '9999D9999', 'nls_numeric_characters = ''.,''')) result
 10  from test
 11  where regexp_like(col, '^\d+\.?\d+$');

    RESULT
----------
    682,78

SQL>