"ON CONVERSION ERROR" fails with "ORA-43918: This argument must be a literal"

user_409172 0
Viewed 763

ON CONVERSION ERROR should allow conversion functions to handle errors such as "ORA-01858: a non-numeric character was found where a numeric was expected". But when I use the feature I only get a different error:

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

The error line and column numbers don't make sense because the code is already using a literal.

oracle12c oracle
1 Answers

CURSOR_SHARING

The ON CONVERSION ERROR feature does not work when the parameter CURSOR_SHARING is set to FORCE. To avoid this error, change the parameter at the system, session, or statement level.

Ideally, CURSOR_SHARING should be set to EXACT for the entire system. But if we have an application that doesn't use bind variables, we probably can't run alter system set cursor_sharing=exact;.

The parameter can be set at the session level with alter session set cursor_sharing=exact;, but it's not always convenient to constantly change session parameters.

The parameter can be changed at the statement level with the hint 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

Parser/optimizer bug

As @gouessej discovered, there is another potential reason for the ORA-43918 error that is not related to cursor sharing. There appear to be parsing or optimizer bugs related to transforming CASE and TO_ functions on some versions of Oracle.

For example, the below SQL statement fails on Oracle 18c and 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

I believe this is a parsing or optimizer bug because the error goes away if you stop transformations by adding a predicate like rownum >= 1. (When Oracle sees ROWNUM, it assumes the results must be displayed in a certain order and will not apply as many transformations to that query block.)

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
user_4091720
Related