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:
|
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'; |