A Custom Event using a date-type substitution value (like `&OLD:WANTED_DELIVERY_DATE`) directly in a PL/SQL date-variable assignment fails with `ORA-01861: literal does not match format string` when triggered through the application, even though the identical logic works fine when tested directly in the backend.

Q: A Custom Event using a date-type substitution value (like `&OLD:WANTED_DELIVERY_DATE`) directly in a PL/SQL date-variable assignment fails with `ORA-01861: literal does not match format string` when triggered through the application, even though the identical logic works fine when tested directly in the backend.

Wrap the substitution value in TO_DATE(...) with an explicit format mask matching how the value is actually substituted (for example, TO_DATE('&OLD:WANTED_DELIVERY_DATE', 'YYYY-MM-DD')) rather than assigning the raw substituted string directly to a DATE variable — the substituted value's string format doesn't necessarily match your session's default NLS date format, which is exactly what produces this literal-format mismatch error when run through the application's own event-firing context versus a manual backend test.

Answer

Wrap the substitution value in TO_DATE(...) with an explicit format mask matching how the value is actually substituted (for example, TO_DATE('&OLD:WANTED_DELIVERY_DATE', 'YYYY-MM-DD')) rather than assigning the raw substituted string directly to a DATE variable — the substituted value's string format doesn't necessarily match your session's default NLS date format, which is exactly what produces this literal-format mismatch error when run through the application's own event-firing context versus a manual backend test.

← Browse Developer Studio, deployment, and upgrades questions