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

Question

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:

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

insert into foo (id, some_number) values('id_01', 8.9);
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

To avoid any culture-specific issues, use [*literals*](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Literals.html#GUID-192417E8-A79D-4A1D-9879-68272D925707) for constants. They have no any culture-dependent treatment and have fixed syntax. For example, numeric literal has a dot (`.`) as a decimal separator and it will be treated exactly the same in any client when you specify: `123.456 + .789`. And they are pure values of the type, there's no any (implicit) conversion — astentx, Nov 21 '22 at 11:46

score 1 · Accepted Answer · answered Nov 21 '22 at 11:36

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>

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

1 Answers1