The ORA-01722: invalid number error message is typically thrown when trying to compare numerical values to non-numerical values:
--
-- Cause errors:
--
select case when 'A' = 1 then 'yes' else 'no' end from dual;
select case when 1 = 'A' then 'yes' else 'no' end from dual;
select * from dual where dummy = 42;
select * from dual where 42 = dummy;
--
-- OK:
--
select case when '2' = 1 then 'yes' else 'no' end from dual;
select case when 1 = '2' then 'yes' else 'no' end from dual;
The error can also be thrown in combination with to_char etc.:
select
to_char('2022-01-01', 'yyyy-mm-dd')
from
dual;
Unfortunately, Oracle does not have a built-in function that checks if a value is numerical. However, it is possible to create a user defined function that returns y if a value can be converted to a number and n otherwise:
create or replace function isnumber(txt in varchar2)
return varchar2
is
num number;
begin
num := to_number(txt);
return 'y';
exception when value_error then
return 'n';
end isnumber;
/
select
isNumber( 5.5 ) a,
isNumber('-42.3' ) b,
isNumber( '17,18') c,
isNumber( 9.1 ') d
from
dual;
Using the DEFAULT val ON CONVERSION ERROR clause of TO_NUMBER
select
supposed_number,
to_number(supposed_number default -9999999999999999 on conversion error)
from
tab
order by
2;
Finding the offending SQL statement with a trace file
By setting the event 01722 (prior to executing the SQL statement!), it is possible to find the offending SQL statement in a trace files:
Find the name of the trace file into which the statement is written:
SQL> select value from v$diag_info where name = 'Default Trace File';
SQL> alter session set events '1722 trace name errorstack level 1';
SQL> select to_number('not a number') from dual;
ORA-01722: invalid number
Concatenation operator
'str' || num evaluates to string. When applying + to the string, it tries to evaluate the string as number which throws ORA-01722 if the string is not convertible to a number.
This is demonstrated with this silly example:
create table tq84_xy ( x number, y number );
insert into tq84_xy values (13, 28);
The following statment throws the error:
select 'The sum of ' || x || '+' || y || ' is ' || x+y || '.' from tq84_xy;
Here, the addition is put into parantheses so that the + is not applied to strings. This statement runs without problems:
select 'The sum of ' || x || '+' || y || ' is ' || (x+y) || '.' from tq84_xy;