It is even possible for this error to appear when there are no numeric columns appearing explicitly in the statement!
Here are some examples: The above statement throws the error message, because it has found a character, in this case, a comma and the default format for TO_NUMBER does not contain a comma.
Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions.
Only numeric fields may be added to or subtracted from dates. We should convert the expression to number before comparing it with number.
The same error can occur when you use arithmetic functions on strings: The fix depends upon the exact expression which caused the problem.
The following guide lists the possible SQL expressions which can give this error, with their most likely cause.
Reference: ORA-01722 exception occurs when we explicitly or simplicity try to convert a character string to a number.
This may occur while inserting/updating in number field is SQL.
The ORA-01722 message is a fairly typical error in Oracle.
The option(s) to resolve this Oracle error are: Only numeric fields or character fields that contain numeric values can be used in arithmetic operations.
Make sure that all expressions evaluate to numbers.
Another mistake often seen is when the user inserts a comma instead of a decimal point in a numerical expression, due to their proximity on the keyboard.
ORA-01722: invalid number Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal.