G-9030

🆓

Warning

Try to define a default value on conversion errors.

Rule Details

Severity Level: Major
Quality Area: Maintainability
Fixing times: 5 mins
Local-medium
Database Support: OracleDB (v12.2.0 +)

Reason

When converting from strings to other datatypes using a conversion function that supports the default ... on conversion error clause, it is a good idea to use this clause to avoid getting an error raised on bad input. The exception can be when you explicitly want an error to be raised to catch and process it in a later exception handler.

Example

Non-Compliant Example

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
      co_employee_id constant employees.employee_id%type := in_employee_id;
      co_dob_str     constant type_up.date_string        := in_dob_str;
   begin
      update employees
         set date_of_birth = to_date(co_dob_str,'FXYYYY-MM-DD')
       where employee_id = co_employee_id;
   end set_dob;
end employee_api;
/

Issues

Line	Column	Message
10	30	Missing conversion error handler in to_date call.

Compliant Solution - ★★★★★

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
      co_employee_id constant employees.employee_id%type := in_employee_id;
      co_dob_str     constant type_up.date_string        := in_dob_str;
   begin
      update employees
         set date_of_birth = to_date(
                co_dob_str default null on conversion error
               ,'FXYYYY-MM-DD'
             )
       where employee_id = co_employee_id;
   end set_dob;
end employee_api;
/

References

same as Trivadis G-9030