Oracle 数字和 varchar 连接
我有一个连接两个表的查询。一个表的列类型为 varchar，另一表的列类型为 number。我已经在 3 个 Oracle 数据库上执行了查询，并且看到了一些奇怪的结果，希望能够得到解释。在其中两个数据库上，类似以下内容的工作。
select a.col1, b.somecol
from tableA a inner join tableB b on b.col2=a.col1;
在此查询中，tableA.col1 的类型为 number，tableB.col2 的类型为 varchar。这在两个数据库中运行良好，但在第三个数据库中则不然。在第三个中我收到（ORA-01722）错误。在第三个中，我需要做类似的事情......
select a.col1, b.somecol
from tableA a inner join tableB b on b.col2=to_char(a.col1);
这适用于所有数据库。我的问题是为什么？上面是一个简化的查询，真正的查询稍微复杂一点，检索大量数据，因此第一个版本要快得多。如果我能让它在所有环境中工作那就太好了。
有谁知道为什么这可以在某些 Oracle 数据库中工作，而在其他数据库中如果没有数据类型的转换则不起作用？是否存在允许这种行为的全局设置？
I have a query that joins two tables. One table has a column that is of type varchar, and the other table has type of number. I have executed my query on 3 oracle databases, and am seeing some strange results I hope can be explained. On two of the databases something like the following works.
select a.col1, b.somecol
from tableA a inner join tableB b on b.col2=a.col1;
In this query tableA.col1 is of type number and tableB.col2 is of type varchar. This works fine in two of the databases but not in the third. In the third I get (ORA-01722) error. In the third I need to do something like...
select a.col1, b.somecol
from tableA a inner join tableB b on b.col2=to_char(a.col1);
This works in all the databases. The question I have is why? The above is a simplified query, and the real query is a little more complex and retrieves a lot of data, hence the first version is much faster. If I could get that to work in all environments it would be great.
Does anyone know why this may work in some oracle databases and not others without the cast on the datatype? Is there a global setting that enables such behavior?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
隐式转换失败的原因之一是连接 varchar 列包含非数字数据。 Oracle 通过转换字符串来处理数字到 varchar2 连接（查看 Gary 在评论中的引用），因此它实际上执行以下操作：
如果 tableB.col2 包含非数字值 - 看起来很可能，它毕竟是一个字符串 - 那么它将抛出
ORA-01722: invalid number。通过显式地将数字列转换为字符串，可以缩短 Oracle 的默认行为。事实上，您在前两个环境中没有遇到此问题是运气问题，而不是配置问题。它可能随时发生攻击，因为它只需要一个非数字字符串即可破坏查询。所以实际上你应该在所有环境中运行显式转换。
至于性能，您可以构建基于函数的索引......
One reason why implicit conversions fail is when the joining varchar column contains data which is not numeric. Oracle handles number to varchar2 joins by converting the strings (check out Gary's citation in his comment), so it actually executes this :
If tableB.col2 contains values which are not numeric - seems quite likely, it is a string after all - then it will hurl
ORA-01722: invalid number. By explicitly casting the number column to a string you short-circuit Oracle's default behaviour.The fact that you don't get this problem in your first two environments is a matter of luck not configuration. It could strike at any time, because it only requires one non-numeric string to break the query. So really you ought to run with the explicit conversion in all environments.
As for performance, you could build a function-based index ...