ORA-01722: invalid number intermittently

Question

We have a SQL query which has a condition like below.

result_value <> 0

The result_value is a varchar column in our Oracle EBS database. The query works fine most of the time. But randomly it throws this below error.

[Error] Execution (216: 33): ORA-01722: invalid number

Changing the condition result_value <> TO_CHAR ('0') makes it work when it errors out.

Any reason why the system behaves like this? Below is a screenshot of the result. The alphanumeric column is a result of regexp_like(result_value , '^-?\d+(.\d+)?$(E(+|-)\d+)?$') then 'numeric' else 'alpha' end alphanumeric

Answer 1

From Oracle 12, you can use TO_NUMBER(result_value DEFAULT NULL ON CONVERSION ERROR) and then you can compare to a number and may not need the regular expression:

SELECT result_value
FROM   table_name
WHERE  TO_NUMBER(result_value DEFAULT NULL ON CONVERSION ERROR) <> 0

Which, for the sample data:

CREATE TABLE table_name (result_value) AS
SELECT '0' FROM DUAL UNION ALL
SELECT '0E0' FROM DUAL UNION ALL
SELECT '0.0' FROM DUAL UNION ALL
SELECT '0E+0' FROM DUAL UNION ALL
SELECT '0.0E-1234' FROM DUAL UNION ALL
SELECT '1' FROM DUAL UNION ALL
SELECT 'ABC' FROM DUAL;

Outputs:

RESULT_VALUE
1

Note: using result_value <> '0' would only exclude one of row of the sample data when there are actually 5 rows with the numeric value identical to 0.

fiddle