8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Conversion Function Enhancements in Oracle Database 12c Release 2 (12.2)
Oracle Database 12c Release 2 (12.2) includes a number of enhancements to datatype conversion functions, making it easier to handle conversion errors.
Setup
The following table is used by the examples in this article.
CREATE TABLE t1 ( data VARCHAR2(20) ); INSERT INTO t1 VALUES ('11111'); INSERT INTO t1 VALUES ('01-JAN-2016'); INSERT INTO t1 VALUES ('AAAAA'); COMMIT;
CAST and TO_* Conversion Functions
In previous database versions failure during data type conversions resulted in an error.
SELECT TO_NUMBER(data) FROM t1; ERROR: ORA-01722: invalid number no rows selected SQL>
In Oracle database 12.2 the CAST
function and several of the TO_*
functions have been amended to include error handling functionality, allowing them to return a default value in the event of a conversion error.
SELECT TO_NUMBER(data DEFAULT -1 ON CONVERSION ERROR) FROM t1; * TO_NUMBER(DATADEFAULT-1ONCONVERSIONERROR) ----------------------------------------- 11111 -1 -1 SQL> SELECT TO_DATE(data DEFAULT '01-JAN-2000' ON CONVERSION ERROR, 'DD-MON-YYYY' ) FROM t1; TO_DATE(D --------- 01-JAN-00 01-JAN-16 01-JAN-00 SQL> SELECT CAST(data AS TIMESTAMP DEFAULT NULL ON CONVERSION ERROR, 'DD-MON-YYYY') FROM t1; CAST(DATAASTIMESTAMPDEFAULTNULLONCONVERSIONERROR,'DD-MON-YYYY') --------------------------------------------------------------------------- 01-JAN-16 12.00.00.000000000 AM SQL>
VALIDATE_CONVERSION Function
The VALIDATE_CONVERSION
function is used to test if a conversion will be successful, making it possible to exclude data that would cause a problem during an operation. The function returns the value "1" if the conversion is successful and "0" if it fails.
SELECT data FROM t1 WHERE VALIDATE_CONVERSION(data AS NUMBER) = 1; DATA -------------------- 11111 SQL> SELECT data FROM t1 WHERE VALIDATE_CONVERSION(data AS DATE, 'DD-MON-YYYY') = 1; DATA -------------------- 01-JAN-2016 SQL>
For more information see:
- CAST
- TO_BINARY_DOUBLE
- TO_BINARY_FLOAT
- TO_DATE
- TO_DSINTERVAL
- TO_NUMBER
- TO_TIMESTAMP
- TO_TIMESTAMP_TZ
- TO_YMINTERVAL
- VALIDATE_CONVERSION
Hope this helps. Regards Tim...