Enhanced CAST Function With Error Handling Oracle Database 12C release 2 (12.2)

CAST function in release 12.2 enables to add error handler DEFAULT return_value ON CONVERSION ERROR in case conversion fails.

Description of GUID-0711298C-420A-42CD-85DE-764C72183572-print.eps follows

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

Leave a Reply

Your email address will not be published. Required fields are marked *