When I run the following query on a postgres database, it works well. But on an oracle database it sends error

ORA-01722: invalid number

This query on oracle had been working till one week ago, and during this week it generates an error.

The general syntax is as below:

SELECT MIN(idd) FROM
(SELECT CAST(id AS INTEGER) idd
 FROM prev_table) new_table

Without the MIN function, it works.

The problem occurs when I use MIN() to get the minimum id.

In this prev_table, id column is defined as varchar, and I want to convert them to integer and then get minimum.

CodePudding user response：

You should not store numbers as text.

From Oracle 12.2, you can use TO_NUMBER(... DEFAULT ... ON CONVERSION ERROR):

SELECT MIN(TO_NUMBER(id DEFAULT NULL ON CONVERSION ERROR)) AS min_id
FROM   prev_table
WHERE  TO_NUMBER(id DEFAULT NULL ON CONVERSION ERROR) IS NOT NULL;

or VALIDATE_CONVERSION:

SELECT MIN(TO_NUMBER(id)) AS min_id
FROM   prev_table
WHERE  VALIDATE_CONVERSION(id AS INTEGER) = 1;

Which, for the sample data:

CREATE TABLE prev_table (id) AS
SELECT 'ABC' FROM DUAL UNION ALL
SELECT '3e2' FROM DUAL UNION ALL
SELECT '1000' FROM DUAL UNION ALL
SELECT '999.9' FROM DUAL UNION ALL
SELECT ' 420' FROM DUAL UNION ALL
SELECT '3e4e' FROM DUAL;

Both output:

fiddle