我知道Oracle（我正在使用Oracle 11 gR 2）可以隐式地将数据类型相互转换。例如，如果我尝试将数字插入varchar区域，它会隐式地将数字转换为varchar，反之亦然，如果varchar是一个有效的数字，它会转换为数字。
Oracle也会在连接时进行这种隐式转换。如果我将一个number列连接到一个varchar列，它会将varchar隐式转换为number并完成查询。但是如果varchar列中有任何无效的数字，它会抛出ORA-01722 : Invalid Number。您可以通过运行以下代码来查看此设置：
create table test_1(
id varchar2(20),
val number);
create table test_2(
id number,
name varchar2(20)
);
insert into test_1 values ('abc', 10);
insert into test_1 values ('1', 11);
insert into test_2 values (1,'abc');
insert into test_2 values (2,'def');
-- Throw error
select
*
from
test_1, test_2
where
test_1.id = test_2.id
-- work
select
test_1.id, val, name
from
test_1, test_2
where
test_1.id = test_2.id
and test_1.id = '1'字符串
您还可以在以下网站上查看和运行示例：http：//sqlfiddle.com/#！4/fdce 3/9/0
现在我的问题是，有没有什么选项或者配置参数可以强制Oracle将这个隐式转换为varchar而不是number？或者确切地看到无效number的错误来源（哪个列或者哪个连接）？
我知道我可以显式地进行转换以避免错误。就像下面一样，但我不想这样解决。
select
*
from
test_1, test_2
where
test_1.id = to_char(test_2.id)型
您也可以访问http：//sqlfiddle.com/#！4/fdce 3/10查看上面的代码是否正常工作。
谢谢
3条答案
按热度按时间yhqotfr81#
“可以显式地进行转换以避免错误......我不希望将其作为解决方案”。
所以你不想使用好的实践？为什么不呢？虽然，如果你正在比较一个数字列和一个字符串列，也许马已经跑了。
ORA-01722是数据库告诉我们一些有用的信息：它告诉我们“你希望
test1.id是数字，但你应该知道它包含非数字值”。现在我们可以解决这个错误。首先，这是正确的吗？我们是否期望
test1.id包含非数字值？如果答案是“否”，那么我们有一个数据质量问题（更不用说数据建模问题），我们应该提出一个bug。但是，如果我们知道
test1.id可以合法地包含非数字字符串，那么我们必须相应地编写查询。这意味着我们需要在WHERE子句的另一端应用to_char()。这不仅可以处理错误，还可以向将来查看查询的同事发出一个小小的标志：“顺便说一下，test1.id包含非数字值：疯狂，哈？”“是否有任何选项或配置参数可以强制Oracle将此隐式转换为varchar而不是number？”
抑制异常总是一个坏主意。我们需要知道什么时候出了问题，这样我们才能正确地处理它。
“是否有任何选项或配置参数来.查看无效数字的错误来源（哪个列或哪个联接）？”
公平地说，期望你熟悉你正在使用的数据模型并不是不合理的。但是如果你不知道是哪些列导致了问题，那么除了查看数据字典（即all_tab_columns）之外，就没有其他选择了。
如果你想找出字符串列中的哪些行包含非数值，那么你需要查询它。在Oracle 12 cR2中有一个非常简洁的
VALIDATE_CONVERSION()函数。了解更多。如果你使用的是早期版本，那么你需要编写自己的函数，比如t his function in another StackOverflow thread。“我在一个ETL团队工作，我对源数据模型没有任何控制权。”
事实上，ETL最大的问题之一是处理来自源系统的错误数据。有各种方法，例如将数据加载到staging表中，验证它，然后根据其质量将其发布到最终表或隔离表。或者我们可以使用DML错误日志记录;这至少会隔离抛出异常的记录。Find out more。
ygya80vv2#
这里已经有了一个很好的答案，但是你可能会发现一个有用的工具是当Oracle的隐式类型转换有问题时给予默认值的能力，例如。
字符串
更多信息和examples here
z9zf31ra3#
你不需要任何特殊的技巧，只需要加入那些“数字”的id，通过使用正则表达式匹配“数字”：
字符串
请参见regexp_like文档。