"ON CONVERSION ERROR"失败并显示"ORA-43918: This argument must be a literal"

Question

ON CONVERSION ERROR应该允许转换函数处理诸如"ORA-01858:在需要数字的地方发现了非数字字符"之类的错误。但是当我使用这个功能时，我只得到一个不同的错误:

SQL> select to_date(the_date default null on conversion error, 'MM/DD/YYYY') the_date
2  from
3  (
4      select '1/1/2021' the_date from dual union all
5      select 'bad date' the_date from dual
6  );
select to_date(the_date default null on conversion error, 'MM/DD/YYYY')
*
ERROR at line 1:
ORA-43918: This argument must be a literal

错误行号和列号没有意义，因为代码已经使用了文字。

Answer 1

CURSOR_SHARING

当参数CURSOR_SHARING设置为FORCE时，ON CONVERSION ERROR特性不工作。要避免此错误，请在系统、会话或语句级别更改该参数。

理想情况下，整个系统的CURSOR_SHARING应该设置为EXACT。但是如果我们有一个不使用绑定变量的应用程序，我们可能无法运行alter system set cursor_sharing=exact;。

该参数可以在alter session set cursor_sharing=exact;的会话级别上设置，但是经常更改会话参数并不总是方便的。

该参数可以通过提示CURSOR_SHARING_EXACT:

在语句级别更改。

SQL> select /*+ cursor_sharing_exact */ to_date(the_date default null on conversion error, 'MM/DD/YYYY') the_date
2  from
3  (
4      select '1/1/2021' the_date from dual union all
5      select 'bad date' the_date from dual
6  );
THE_DATE
---------
01-JAN-21

解析器/优化器错误

正如@gouessej所发现的，ORA-43918错误还有另一个与游标共享无关的潜在原因。在某些版本的Oracle中，似乎存在与转换CASE和TO_函数相关的解析或优化器错误。

例如，下面的SQL语句在Oracle 18c和19c上失败:

SQL> select case when v_num is null then 0 else v_num end
2  from
3  (
4      select to_number('120.3' default null on conversion error, '99999D99') as v_num
5      from dual
6  );
select to_number('120.3' default null on conversion error, '99999D99') as v_num
*
ERROR at line 4:
ORA-43918: This argument must be a literal

我相信这是一个解析或优化器错误，因为如果您通过添加像rownum >= 1这样的谓词来停止转换，错误就会消失。(当Oracle看到ROWNUM时，它假设结果必须以一定的顺序显示，并且不会对该查询块应用那么多转换)

SQL> select case when v_num is null then 0 else v_num end
2  from
3  (
4      select to_number('120.3' default null on conversion error, '99999D99') as v_num
5      from dual
6  where rownum >= 1
7  );
CASEWHENV_NUMISNULLTHEN0ELSEV_NUMEND
------------------------------------
120.3