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:

It works as expected. We can even write this query as following:

Both of them will work, but they will work in different ways. Second query will cause Oracle to convert values of the “userno” column to number! Unfortunately my reader told me that he must use a query like the second one, otherwise he needs to change lots of codes in application. He wonders why he gets an error now although this type of query used for a long time.

Answer is simple, the table has some values that can not be converted to number. “SELECT * FROM sample WHERE userno = 1” will work like “SELECT * FROM sample WHERE TO_NUMBER(userno) = 1” because of implicit conversion.

So if we have some values in userno column that can not be converted to number, our query will give ORA-01722.

If we have no chance to change the query (although it’s highly recommended), we need to find the rows that will cause the error.

Here’s a simple (and sample) is_number function:

After we create the function, let’s find the failing records:

They used this method to find and fix failing records to solve the problem.

Please share

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.

Leave Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.