ORA-01722: invalid number when inserting decimal number in single quotes with

I’m getting error ORA-01722: invalid number with following query:

insert into foo (id, some_number) values('id_01', '8.9');

What I’ve found so far:

1. I can run the query if I remove single quotes, like:

   MEDevel.com: Open-source for Healthcare and Education

   Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

   Visit Medevel

   insert into foo (id, some_number) values(‘id_01’, 8.9);

2. Or if I change . to , like:

   insert into foo (id, some_number) values(‘id_01’, ‘8,9’);

I’m using Oracle database.

Funny thing: I’m located in Spain, where decimal numbers are written with , instead of .
Another developer in my team, not located in Spain can insert those values with the first query (with single quotes and .) which leads me to think this might be due to some system properties. My computer language is English, macOS Monterey 12.5.1

TABLE: FOO

Columns
NAME            DATA TYPE           NULL  DEFAULT    COMMENTS
*ID             VARCHAR2(20 BYTE)   No
 SOME_NUMBER    NUMBER(3,1)         Yes

>Solution :

Looks like issue with national language support, i.e. NLS_NUMERIC_CHARACTERS.

SQL> create table foo (id varchar2(20), some_number number(3, 1));

Table created.

SQL> insert into foo(id, some_number) values ('id_01', '8.9');
insert into foo(id, some_number) values ('id_01', '8.9')
                                                  *
ERROR at line 1:
ORA-01722: invalid number

Failed (as you already know). If nls_numeric_characters is set to accept

• dot as a decimal character and
• comma as a group separator,

then it works:

SQL> alter session set nls_numeric_characters = '.,';

Session altered.

SQL> insert into foo(id, some_number) values ('id_01', '8.9');

1 row created.

SQL>