CAST function in release 12.2 enables to add error handler DEFAULT return_value ON CONVERSION ERROR in case conversion fails.
Simple example
CREATE TABLE test_tbl ( id1 VARCHAR2(10) ); INSERT INTO test_tbl VALUES('1'); INSERT INTO test_tbl VALUES('2'); INSERT INTO test_tbl VALUES('A'); COMMIT; SELECT * FROM test_tbl; ID1 ---------- 1 2 A
As default CAST to NUMBER raises error for value A.
SELECT CAST(id1 AS NUMBER) num FROM test_tbl; SQL Error: ORA-01722: invalid number
with new extension it can be simple avoided
SELECT id1, CAST(id1 AS NUMBER DEFAULT -900 ON CONVERSION ERROR) num FROM test_tbl; ID1 NUM ---------- ---------- 1 1 2 2 A -900
Have a fun 🙂
Tomasz