SQL Code running perfectly in oracle 11g not running after upgrading to 19c

In 11g, this code is running and returning a value. But after upgrading to 19c, this code returns an error ORA-01843: not a valid month.

note: if we comment rn=1 (where condition in out block), then the query is running

Solution 1:

Don't compare dates to strings. Should be

and trunc(e.created_date) = date '2021-12-19'
                            -----------------
                            -- This is DATE datatype

because - what you used, is wrong

and trunc(e.created_date) = ('19-DEC-21')
                            -------------
                            -- This is a STRING

Why is it wrong? Because it depends on NLS settings. Apparently, those two databases differ in them. You were "lucky" in 11g as Oracle managed to implicitly convert that string to a valid DATE datatype value.