ORACLE-023: annoying ora-01722 invalid number

I used to write sql statements for a period of time, but I always reported ora-01722 inexplicably.

It's frustrating for this error prompt, especially for a complex sql statement, because most of the time you don't know what's going on.

Through the summary of each encounter, in fact, it is not so difficult to solve, the main reasons are:

1. Assign values of two types that do not match (one numeric type, one non-numeric type, the same below)

2. Compare two values with mismatched types (for example, "=")

3. The value in the to_number function is non-numeric, for example, to_number ('a') is definitely not good, and to_number ('12306') is normal.

To avoid these problems, it is necessary to seriously deal with different types of problems when writing sql statements.

For example, if you want to compare, both use to_number cast (to_number (field a) = to_number (field b))

Or convert it to a string type at the same time (field a | |'= field b | |'', all concatenate an empty string to make it a string type).

When using the to_number function in a statement, make sure that the value must be in numeric format, or write exception handling.

When we encounter this error prompt, we start with the fields of all the numeric types used and check them one by one to solve the problem.