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.
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
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