Oracle-ORA-01722 invalid number Error

Suddenly one day, good system error: ORA-01722 invalid number

Environment: Oracle9 + JDBC access to the database. It was okay before. This error was suddenly found.

Later I learned why

1. The following SQL statement is executed in the Code:

Select mdn from tablename where mdn = 13800000000

The mdn field in the tablename table is a varchar2 () type field. Because of the implicit type conversion function of Oracle fields

The preceding SQL statement is usually feasible. When oracle parses this condition during a query, it first compares to_number (mdn) with where mdn = 13812345678, if we can ensure that all numbers are stored in the mdn field, this statement will never be reported (there may be efficiency problems), but some people mistakenly add several letters 133aa000000 to the mdn, if the above statement still exists and the table does not contain 13800000000 of the user's data, when the query scans for 133aa000000, to_number (mdn) reports an error in the ORA-01722 invalid number

2. Therefore, it is best to write SQL statements properly:

Select mdn from tablename where mdn = '201312'

No problems!