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

Question

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?

*: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](https://stackoverflow.com/q/19572759/146325) 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, Dec 05 '19 at 12:20
@APC: Did you mean `VALIDATE_CONVERSION`? (And thanks - I'm glad I'm not the only one... :-) — Bob Jarvis - Слава Україні, Dec 05 '19 at 12:35

GMB · Answer 1 · 2019-12-05T13:42:02.200

3

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;

edited Dec 05 '19 at 13:42

answered Dec 05 '19 at 13:22

GMB

216,147
25
84
135

1

I wonder what made them stick to this verbose "magic" syntax instead of some optional fourth "ifConversionErrror" paramter. – Kirill Leontev Dec 05 '19 at 13:46

score 2 · Accepted Answer · answered Dec 05 '19 at 12:26

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>

Thanks for the answer it works fine, but my varchar2 columns contain numeric decimal values when I divide them by 100, getting Invalid number issues again. `select mycolumn/100 from mytable;` — champ.exe, Dec 09 '19 at 07:07
You're welcome. As of the problem you have: see whether REPLACE helps. Replace all decimal "comma" characters to "period" (i.e. instead of `12,45` you'll have `12.45`), and then try again. Or, if it is not about those characters, see which ones you really have. Basically, such problems iswhat you can expect when storing numbers into VARCHAR2 columns. Try not to do that. — Littlefoot, Dec 09 '19 at 07:14
Littlefoot Thanks for your feedback. we have fixed view on UAT and its working fine. But on production its not working. both UAT and production hosted in FR locale where we are getting this error. — champ.exe, Dec 12 '19 at 11:44
Unfortunately, there's no easy way out of it. One option is to use a **loop** and apply the above code to each row separately. Doing so, you'd be able to log & skip values that have failed (and deal with them later, manually). — Littlefoot, Dec 12 '19 at 20:26

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

2 Answers2