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
1. Make it Easier to Convert
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 inserted 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.
2. Use VARCHAR2
You cannot always depend on unpredictable implicit conversion, sometimes, you should change the column from NUMBER into VARCHAR2. For example, phone numbers of customers may not be perfectly formatted as NUMBER.
SQL> insert into customers (cust_id, phone_number) values (100, '0254 539 2413');
insert into customers (cust_id, phone_number) values (100, '0254 539 2413')
*
ERROR at line 1:
ORA-01722: invalid number
As we can see, the value of phone number cannot be converted into a NUMBER, so we should use VARCHAR2 instead.
SQL> alter table customers modify phone_number varchar2(13);
Table altered.
SQL> insert into customers (cust_id, phone_number) values (100, '0254 539 2413');
1 row created.
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 was trying to do an arithmetic operation, but the string 'Today is ' failed to be converted into number. 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.