动态SQL，相同的from和where子句，但是不同的select子句，一个工作的，不工作的

Question

我经历了一次工作失败。在“工作”中，PLS被调用为offcid文件和假脱机文件。我检查了假脱机文件，错误是无效的。在PLS中单独运行查询时，通过传递offcid，我发现错误出现在一个select查询中，其中where子句中动态地传递了offcid。

where offcid = &2;

在进一步检查细节时，我发现在from和where子句方面还有一个相同的查询，只有select子句不同(没有group by)，但运行良好。在这份工作中，那个坏家伙被通过了43名。根据高级开发人员的建议，我们扫描了该表，发现源表中的一条记录在offcid列中有字符值，我们删除了它，然后查询运行良好。

还有一件事是，我只在dev中的生产环境中遇到了这个错误，相同的查询在相同的场景下运行得很好。有人知道是什么导致了这个问题吗？甲骨文11g是否有可能导致这种情况的后端检查？

Answer 1

因此，offcid被定义为VARCHAR2而不是数字列？如果是这样的话，您可能希望将替换变量包装在引号中，以便传入的值也被视为字符。(是否应该将该列定义为数字列则是另一回事。)

如果我理解您的问题，您想知道为什么一个环境在一个环境中遇到“无效编号”错误，而不是另一个环境，假设这两个环境都有相同的数据？

如果尝试将字符串与数字进行比较，Oracle将隐式地尝试首先将字符值转换为数字，并将结果与数字进行比较。也就是说，像这样的：

SELECT * FROM dual WHERE '1' = 1 

将按以下方式进行实际评估：

SELECT * FROM dual WHERE TO_NUMBER('1') = 1

在这里，字符串'1‘可以转换成一个数字，一切都很好。

在您的情况下，如果以10代替&2，则您的查询将被计算为：

SELECT ... where TO_NUMBER(offcid) = 10

即使表中有不能转换为数字的不确定值，也可以间歇性地获得“无效数字”错误的原因取决于Oracle是否实际考虑包含不能转换为数字的值的行。所有这些都取决于用于查询的执行计划。

假设您有这样的查询：

SELECT ... FROM some_table where TO_NUMBER(offcid) = 17 AND another_column = 4

让我们假设您的冒犯行有offcid = 'ONE7‘和another_column =2

如果甲骨文在some_table上进行全表扫描，它将比较所有行，当它碰到值不能转换为数字的行(例如'XYZ')时，您将遇到错误。但是，如果您在another_column上有一个索引，甲骨文决定首先用another_column = 4来隔离行，那么它不会查看带有another_column =2的行，也不会尝试将'XYZ‘转换成一个数字，这样就不会得到错误。

可以使用完全相同的查询拥有两个数据完全相同的环境，但是由于使用的执行计划不同(已知数据库版本会导致优化器工作方式的更改，从而影响查询计划)您的查询可以在一个环境中运行得很好，但在另一个环境中失败。

这有意义吗？