Lin Hong's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

Oracle 12c 新特性 validate_convertion Tips

2018-07-26

Oracle 12c 新特性 validate_conversion Tips

12cR2引入validate_conversion 函数的使用注意点

测试表test的创建和插入测试数据

HR@PDB1> create table test( c_to_varchar2 varchar2(10),c_to_char number, c_to_date char(40));

Table created.

HR@PDB1> insert into test values('A',100,'2018-07-28 10:11:12');

1 row created.

HR@PDB1> insert into test values('1',100,'2018-07-28 10:11:12');

1 row created.

HR@PDB1> select * from test;

C_TO_VARCH  C_TO_CHAR C_TO_DATE
---------- ---------- ----------------------------------------
A                 100 2018-07-28 10:11:12
1                 100 2018-07-28 10:11:12

HR@PDB1> desc test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C_TO_VARCHAR2                                      VARCHAR2(10)
 C_TO_CHAR                                          NUMBER
 C_TO_DATE                                          CHAR(40)

HR@PDB1>

在创建test01时候报错,因为c_to_nvarchar2字段内容含有非number,转换不了number的数据

HR@PDB1> create table test01 as select to_number(c_to_nvarchar2) nbr from test;
create table test01 as select to_number(c_to_nvarchar2) nbr from test
										*
ERROR at line 1:
ORA-00904: "C_TO_NVARCHAR2": invalid identifier


HR@PDB1> 

通过validate_conversion()函数条件进行判断是否可以转换。

HR@PDB1> create table test01 as select to_number(c_to_varchar2) nbr from test where validate_conversion(test.c_to_varchar2 as number) = 1;

Table created.

HR@PDB1> 
HR@PDB1> select * from test01;

	   NBR
----------
		 1

HR@PDB1> 
HR@PDB1> desc test01;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NBR                                                NUMBER

HR@PDB1> 

另外需要注意的是 validate_conversion 判断类型转换对象范围如下,不然报 ORA-43908 转换number类型为char一直以来都是OK的,提供这个函数是为了判断能否转换为 number等类型

HR@PDB1> select * from test where validate_conversion(test.c_to_char as char) = 0;
select * from test where validate_conversion(test.c_to_char as char) = 0
											 *
ERROR at line 1:
ORA-43908: invalid output data type
HR@PDB1> !oerr ora 43908
43908, 0000, "invalid output data type"
// *Document: YES
// *Cause:    An invalid output data type was provided.
//            The valid output data types for VALIDATE_CONVERSION operator and
//            the operators supporting DEFAULT ON CONVERSION ERROR syntax are:
//            NUMBER, BINARY_FLOAT, BINARY_DOUBLE, DATE, TIMESTAMP,  
//            TIMESTAMP WITH TIME ZONE, INTERVAL DAY TO SECOND, 
//            INTERVAL YEAR TO MONTH.        
// *Action:   Provide a valid output data type.

HR@PDB1> 

时间字段的判断,注意写法

2月份没有30日,所以最后一行记录判断为不是正确date

HR@PDB1> select * from test;

C_TO_VARCH  C_TO_CHAR C_TO_DATE
---------- ---------- ----------------------------------------
A                 100 2018-07-28 10:11:12
1                 100 2018-07-28 10:11:12
B                 200 February 20, 2018, 10:30
B                 200 February 30, 2018, 10:30

HR@PDB1> desc test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C_TO_VARCHAR2                                      VARCHAR2(10)
 C_TO_CHAR                                          NUMBER
 C_TO_DATE                                          CHAR(40)

HR@PDB1> select * from test where validate_conversion(c_to_date as date) = 1;

no rows selected

HR@PDB1> select * from test where validate_conversion(c_to_date as date, 'Month dd, YYYY, HH24:MI','NLS_DATE_LANGUAGE = American') = 1;

C_TO_VARCH  C_TO_CHAR C_TO_DATE
---------- ---------- ----------------------------------------
B                 200 February 20, 2018, 10:30

HR@PDB1> select * from test where validate_conversion(c_to_date as date, 'yyyy-mm-dd hh24:mi:ss','NLS_DATE_LANGUAGE = American') = 1;

C_TO_VARCH  C_TO_CHAR C_TO_DATE
---------- ---------- ----------------------------------------
A                 100 2018-07-28 10:11:12
1                 100 2018-07-28 10:11:12

HR@PDB1> 

To be continue….

Have a good life! 2018/07 via LinHong


Similar Posts

Comments