Search notes:

ORA-43918: This argument must be a literal

The ORA-43918: This argument must be a literal can occur when using the default … on conversion error clause to prevent ORA-01858: A non-numeric character was found instead of a numeric character.

alter session set nls_date_format = 'dd.mm.yyyy';

Check if we're running with the default value for cursor_sharing:

select
   value,
   default_value
from
   v$parameter
where
   name = 'cursor_sharing';
--   
-- VALUE     DEFAULT_VALUE
-- -------   -------------
-- EXACT     EXACT

The following query runs ok, as expected:

select
   dt,
   to_date(dt default null on conversion error, 'yyyy-mm-dd') dt_
from (
   select '2024-12-28'   dt from dual union all
   select 'no such date' dt from dual
);
--
-- DT           DT_                
-- ------------ -------------------
-- 2024-12-28   2024-12-28 00:00:00
-- no such date

So does this:

select
   to_date(dt default null on conversion error, 'yyyy-mm-dd') dt_
from (
   select '2024-12-28'   dt from dual union all
   select 'no such date' dt from dual
);
--
-- DT_                
-- -------------------
-- 2024-12-28 00:00:00
--

When setting cursor_sharing to force …

alter session set cursor_sharing = force;

… things change a bit. The following statement throws ORA-43918: This argument must be a literal:

select
   dt,
   to_date(dt default null on conversion error, 'yyyy-mm-dd')
from (
   select '2024-12-28'   dt from dual union all
   select 'no such date' dt from dual
);

Interestingly, this one runs ok:

select
   to_date(dt default null on conversion error, 'yyyy-mm-dd') dt_
from (
   select '2024-12-28'   dt from dual union all
   select 'no such date' dt from dual
);
--
-- DT_                
-- -------------------
-- 2024-12-28 00:00:00
--

We can use the cursor_sharing_exact hint so that this query runs ok:

select /*+ cursor_sharing_exact */
   dt,
   to_date(dt default null on conversion error, 'yyyy-mm-dd')
from (
   select '2024-12-28'   dt from dual union all
   select 'no such date' dt from dual
);

ORA-43918: This argument must be a literal

See also