Oracle NVL无效号码

Question

我有两个Oracle12c (12.1.0.2.0)数据库，其中一个为以下查询返回'ok' (使用SQL 3.2.20.10)，而另一个在ORA-01722: invalid number中返回结果

select 'ok' from dual where 1 = nvl(1, 'X');

NVL的Oracle说：

如果expr1是数字的，那么Oracle将确定哪个参数具有最高的数值优先级，将另一个参数隐式转换为该数据类型，并返回该数据类型。

NLS_COMP、NLS_SORT和NLS_LANGUAGE的值在两个数据库之间是相同的，因此它们不应该导致两个参数的数值优先级不同。这两个数据库之间有什么不同会导致一个返回'ok'，另一个导致错误？

Answer 1

cursor_sharing可能是关键因素。

谓词"1 = nvl(1，'X')“可以在解析时计算，如果它总是以文字形式执行，并且优化为true或false。但是，如果cursor_sharing是强制的，那么所有三个文本都可以替代其他值，表达式直到执行才能被计算。

我不得不使用两个独立的本地表来测试它。

alter session set cursor_sharing=force;
create table me_dual as select * from dual;
select 'ok' from me_dual x where 1 = nvl(1, 'A');
select 'ok' from me_dual x where 1 = nvl(1, 'A')

ERROR at line 1:
ORA-01722: invalid number
                                               *
alter session set cursor_sharing=exact;
create table alt_dual as select * from dual;
select 'ok' from alt_dual x where 1 = nvl(1, 'A');

'O
--
ok

Answer 2

将NVL的列参数包装在“to_char”中，解决了“ora-01722:无效数字”错误的问题：

select 'ok' from dual where 1 = nvl(to_char(1), 'X');

Answer 3

真可爱..。这不是一个答案，但我不能在评论中以任何形式的可读性清晰地发布这篇文章。

这都是在同一个实例(oracle 11)上运行的.但我认为这也说明了类似的问题。

  SQL> select version from v$instance;

  VERSION
  -----------------
  11.2.0.4.0

  SQL> select * from dual where 1 = nvl(1,'X');

  D
  -
  X

  SQL> select nvl(1,'X') from dual;
  select nvl(1,'X') from dual
               *
  ERROR at line 1:
  ORA-01722: invalid number


  SQL>

我记得以前见过这个，但不记得怎么解释了。O.o

不确定为什么在WHERE和SELECT子句之间似乎对NVL进行了不同的评估。