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:
|
1 2 3 4 5 |
CREATE TABLE sample ( userno VARCHAR2(10)); INSERT INTO sample VALUES ( '1' ); INSERT INTO sample VALUES ( '5' ); INSERT INTO sample VALUES ( '10' ); COMMIT; |
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:
|
1 |
SELECT * FROM sample WHERE userno = '1'; |
It works as expected. We can even write this query as following:
|
1 |
SELECT * FROM sample WHERE userno = 1; |
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.
|
1 2 3 4 5 6 |
INSERT INTO sample VALUES ( 'Gokhan' ); SELECT * from sample where userno = 1; ERROR: ORA-01722: invalid number |
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:
|
1 2 3 4 5 6 7 8 |
CREATE OR REPLACE FUNCTION is_numeric(str VARCHAR2) RETURN NUMBER IS v_number NUMBER(38); BEGIN v_number := TO_NUMBER(str); RETURN 1; EXCEPTION WHEN value_error THEN RETURN 0; END; |
After we create the function, let’s find the failing records:
|
1 2 3 4 5 |
SELECT userno FROM sample WHERE is_numeric(userno) = 0; USERNO ---------- Gokhan |
They used this method to find and fix failing records to solve the problem.
