How to Resolve ORA-01722: invalid number

  • by

ORA-01722

ORA-01722 means that the arithmetic operation in the statement failed to calculate because one of operands cannot be converted to a valid number implicitly.

Let's see some error patterns.

A. Type Conversion

We created a simple table containing only one column with NUMBER type.

SQL> create table salaries (salary number);

Table created.

When we tried to insert a row into the table containing NUMBER column, we got ORA-01722.

SQL> insert into salaries (salary) values ('200,000');
insert into salaries (salary) values ('200,000')
                                      *
ERROR at line 1:
ORA-01722: invalid number

This is because the value '200,000' of column SALARY cannot be converted into a valid number. We should make it easier to be converted, so we remove the comma separator.

SQL> insert into salaries (salary) values ('200000');

1 row created.

Unsurprisingly, string '200000' can be converted to a number and insert into the table.

In fact, implicit conversion between different data types has some restrictions, I suggest that you use explicit conversion to make your codes robust.

B. String Concatenation

Using a plus (add) sign can not concatenate strings.

SQL> set heading off;
SQL> select 'Today is ' + sysdate from dual;
select 'Today is ' + sysdate from dual
       *
ERROR at line 1:
ORA-01722: invalid number

SQL parser thought your statement tried to make an arithmetic operation, but it failed to continue. The correct way to concatenate strings is to use ||, not a plus sign +.

SQL> select 'Today is ' || sysdate from dual;

Today is 26-DEC-19

As a result, the output is perfect in concatenating a string and a date without ORA-01722.

A very similar error that you might see in your PL/SQL codes is ORA-06502: PL/SQL: numeric or value error, which is also related to conversion issues of numeric values.

Leave a Reply

Your email address will not be published. Required fields are marked *