转换错误失败,并返回"ORA-43918:此参数必须是文字参数(&Q;

2022-04-14 00:00:00 oracle12c oracle

ON CONVERSION ERROR应该允许转换函数处理错误,如&Quot;ORA-01858:在需要数字的地方发现了非数字字符&Quot;。但当我使用该功能时,我只收到一个不同的错误:

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

错误行号和列号没有意义,因为代码已经在使用文本。


解决方案

游标共享

当参数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上,似乎存在与转换CASETO_函数相关的分析或优化器错误。

例如,以下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

相关文章