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 referred to: http://davidyu720.itpub.net/post/31716/291191 to know the reason

1.CodeThe following SQL statement is executed:

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, the to_number (MDN)

Make a comparison with where MDN = 13812345678. If the MDN field contains all numbers

Therefore, this statement will never be reported (there may be efficiency problems), but some people mistakenly add it to the MDN.

Several letters: 133aa000000. If the preceding statement still exists and the table does not contain 13800000000 of the user 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!