SQL Select Error: “ORA-01722: invalid number” only when number is queried

Question

Other questions about this error message seem to be in situations where an insert is being performed or text is being converted to a number. In my case, when I select by a specific number from my_table I get the error "ORA-01722: invalid number" and when I use a different number I don't.

This code triggers the error message:

SELECT * FROM my_table WHERE sel_num = 156396255

This code returns the expected results (1 row from my_table):

SELECT * FROM my_table WHERE sel_num = 156396320

The sel_num field is defined in my_table as:

Column Name: sel_num, Data Type: VARCHAR2 (30 byte), Null?: Y

I have no control over the data type unfortunately.

Oracle states that this error is caused by:

"The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates."

This is confusing considering I am passing a number into the WHERE clause, not a string. Due to the VARCHAR2 data type, I tried passing the number as a string into the query and this caused the error message to go away.

This code returns an empty set with no error message:

SELECT * FROM my_table WHERE sel_num = '156396255'

This may an acceptable result if this specific value for sel_num does not exist in the table, but I am unsure why this seems to be working the way it does.

Answer 1

There is nothing confusing about the version where you provide a number value in the WHERE clause. Because your value is a number Oracle attempts to convert the varchar2 column values into a number data type for comparison. Some of the columns do not contain valid numeric data so an error occurs. If you enclose the value you want to search for in single quotes now you are doing a character to charter compare. Problem if the table data column stores any non-digits in the value which then match your value not match. So you may have to filter for leading and trailing spaces, remove dollar signs or commas etc... This is an example of 1) why the appropriate database data type should always be used to store data and 2) why data should always be normalized (edited): case, leading and/or trailing spaces, zero filled, etc....

Answer 2

Why does the second number work? In your second case the value provided was converted and met the filter criteria, there was no need to try implicit conversion on any more values.

If you are using Oracle 12r2 or newer you can find the invalid rows with VALIDATE_CONVERSION() or the robust cast functions

Example:

WITH test_case AS(
SELECT '1' AS my_field FROM dual UNION ALL
SELECT '2' FROM dual UNION ALL
SELECT 'string' FROM dual )
SELECT
    my_field,
    VALIDATE_CONVERSION(my_field AS NUMBER)AS is_number,
    CAST(my_field AS NUMBER DEFAULT 0 ON CONVERSION ERROR)AS conversion_result
FROM
    test_case;

Result:

Answer 3

use trim() in column in times of the condition because it may contain space otherwise no reason of returning row

   SELECT * FROM my_table WHERE trim(sel_num) = '156396255'

Answer 4

The data on the table for the field you are testing has alpha-numeric characters in it. When you test with a numeric this error is thrown. Then you have to test by adding quotes or anything else that will tell oracle you have a character string. If nothing is returned then you have to adjust your query, an example being extra spaces.

  SELECT * FROM my_table WHERE TRIM(sel_num) = '156396255'