G-9040: Try using FX in string to date/time conversion format model to avoid fuzzy conversion.

Minor

Reliability, Testability

Reason

The default string-to-date conversion rules allow fuzzy conversion when converting from strings to date or timestamp datatypes (using to_date, to_timestamp, to_timestamp_tz or cast to any of those datatypes). For example you can omit punctuation characters, use any non-alphanumeric character for punctuation, use month name instead of number, or various other rules.

In practice you almost always expect a truly fixed format and want the database to enforce the format model and raise an error if the data does not match the format model. This you can achieve by adding the format modifier FX (format exact).

The exception to this rule can be if you are converting textual input typed by a user, in which case the fuzzy conversion may be what you want.

Example (bad)

create or replace package body employee_api is
   procedure set_dob(in_employee_id in employees.employee_id%type
                    ,in_dob_str     in varchar2) is
   begin
      update employees
         set date_of_birth = to_date(in_dob_str,'YYYY-MM-DD')
       where employee_id = in_employee_id;
   end set_dob;
end employee_api;
/

Example (good)

create or replace package body employee_api is
   procedure set_dob(in_employee_id in employees.employee_id%type
                    ,in_dob_str     in varchar2) is
   begin
      update employees
         set date_of_birth = to_date(in_dob_str,'FXYYYY-MM-DD')
       where employee_id = in_employee_id;
   end set_dob;
end employee_api;
/