Getting Error Running Sql ID ORA-01722 Invalid Number

SYMPTOMS

In Oracle Balance & Control, you get the following error when running a SQL ID:

ORA-01722: invalid number

As a result, the expected data is not updated by the SQL ID.

CAUSE

Null values exist in columns accessed by the code in the SQL ID.

SOLUTION

Review all columns accessed by the code in the SQL ID for null values.  Update NULL values to zero or another appropriate number value.

Example:

select count(*) from mortgages where origination_fee is NULL;
update mortgages set origination_fee=0 where origination_fee is NULL;


1. We should try to avoid implicit conversion.
2. We should compare with same datatypes.
3. While comparing different datatypes, we should first do explicit conversion.
4. We should not store numbers in varchar2 fields.
5. We should convert the expression to number before comparing it with number.