DBA Hub

📋Steps in this guide1/3

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.

oracle 12cconfigurationintermediate
by OracleDba
12 views
1

Setup

The following table is used by the examples in this article.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
CREATE TABLE t1 (
  data VARCHAR2(20)
);

INSERT INTO t1 VALUES ('11111');
INSERT INTO t1 VALUES ('01-JAN-2016');
INSERT INTO t1 VALUES ('AAAAA');
COMMIT;
2

CAST and TO_* Conversion Functions

In previous database versions failure during data type conversions resulted in an error. In Oracle database 12.2 the function and several of the functions have been amended to include error handling functionality, allowing them to return a default value in the event of a conversion error.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
SELECT TO_NUMBER(data)
FROM   t1;
ERROR:
ORA-01722: invalid number

no rows selected

SQL>

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>
3

VALIDATE_CONVERSION Function

The 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. 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...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!