无论你做了多少测试（嗯，它实际上是这样，但这是一个完全不同的问题），你几乎可以保证，在某些时刻，你那些漂亮的数据验证代码，在解析从Web表单输入的数据或从外部加载数据文件时，会弹出错误：

 

 

当然，这里真正令人讨厌的是，你不知道哪个列的值（假设你有多个数字列）。

 

在数据加载期间管理转换错误

 

怎么办？ 当然，明智的是，在你的代码中添加大量的数据验证检查，以尝试捕获来自数据源的错误类型数据所到达的情况。很可能所有的附加验证检查将减慢插入数据的过程，但这不是一个很好的结果。

 

如果您的数据通过外部文件到达，那么您可以使用BADFILE子句捕获那些由于数据类型错误而无法加载的记录。 但是，如果插入语句的数据源是由ETL作业填充的中间表或来自网页表单的一系列值，又该怎么办？

 

如何在INSERT期间管理转换错误

​

Panic over - Database 12c版本2包含了对CAST和TO_xxx函数的重要更改，以管理最常见的数据转换错误。 如果存在转换错误，CAST函数现在可以返回用户指定的值。

 

例如，让我们在模式中构建一个简单的计划表：

 

并让我们插入一些数据，其中包括在我们尝试将值添加到目标表中时可能导致数据转换错误的值：

 

现在让我们试着将数据从我们的分期表插入到EMP表，看看会发生什么：

 

 

……毫不意外的，我得到了以下错误：

 

 

我可以用几种不同的方式来处理这种情况。首先，尝试并发现临时表中的哪些行和列会包含导致数据转换错误的值。为此，我将使用新的VALIDATE_CONVERSION（）函数，该函数标识无法转换为所需数据类型的问题数据。如果给定的表达式可以转换为指定的数据类型，则返回1，否则返回0。

 

 

这将产生一个表，其中我可以轻松地选择数据转换将要成功（列值为1）和失败（列值为0）的行：

 

 

我可以使用此信息过滤临时表中的数据，因为我将其插入到我的EMP表或我可以使用INSERT INTO ... .. SELECT语句中增强的CAST和TO_xxx函数。

 

当发生数据类型转换错误时，CAST函数（以及TO_NUMBER，TO_BINARY_FLOAT，TO_BINARY_DOUBLE，TO_DATE，TO_TIMESTAMP，TO_TIMESTAMP_TZ，TO_DSINTERVAL和TO_YMINTERVAL函数）现在可以返回用户指定的值，而不是错误。这减少了数据转换和数据加载过程中的故障。

 

因此，我新的12.2版本验证SELECT语句如下所示：

 

 

这五行结果插入到我的EMP表中 - 显然这意味着在插入过程（行1,4,6和8）期间拒绝了第4行，因为它们包含将内容转换为empno键的一个数字错误。 这里是加载的数据：

 

 

我们可以看到在第1行，HIERDATE无效，所以它被替换为sys日期（07-JUL-16）的值。 第2行，DEPTNO的值是转换默认值99，而在第4行，MGR的值是转换默认值9999。

 

总结

 

增强的CAST函数（以及TO_NUMBER，TO_BINARY_FLOAT，TO_BINARY_DOUBLE，TO_DATE，TO_TIMESTAMP，TO_TIMESTAMP_TZ，TO_DSINTERVAL和TO_YMINTERVAL函数）可帮助您处理数据转换错误，而无需使用复杂的PL / SQL代码或在应用程序代码中写入数据验证例程。

 

新的VALIDATE_CONVERSION（）函数可用于帮助您标识无法转换为所需数据类型的列值。

 

这两个功能都很有用。希望你会喜欢！

原文发布时间为：2017-03-24

本文来自云栖社区合作伙伴DBAplus