Implicit Conversion And ORA-01722 – Gokhan Atil’s Blog

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:

CREATE TABLE sample ( userno VARCHAR2(10));
INSERT INTO sample VALUES ( '1' );
INSERT INTO sample VALUES ( '5' );
INSERT INTO sample VALUES ( '10' );
COMMIT;

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:

SELECT * FROM sample WHERE userno = '1';

1	SELECT * FROM sample WHERE userno = '1';

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

SELECT * FROM sample WHERE userno = 1;

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.

INSERT INTO sample VALUES ( 'Gokhan' );

SELECT * from sample where userno = 1;

ERROR:
ORA-01722: invalid number

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:

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;

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:

SELECT userno FROM sample WHERE is_numeric(userno) = 0;

USERNO    
----------
Gokhan

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.

Please share

Leave Comment Cancel reply