Implicit Conversion And ORA-01722

I’ve an email from a reader that he gets ORA-01722 error from a query. he says that the query works well on same structured tables on different schemas. After a short conversation, I figured out that it’s about implicit conversion mechanism.

Let’s create a sample table to demonstrate the problem:

You may notice that we use “varchar” column to keep “number” values. That was the exact case my reader faced.

Let’s write a simple query: