G-9030: Try to define a default value on conversion errors.
Major
Maintainability, Reliability, Testability
Restriction
Oracle Database 12c Release 2
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 (bad)
1
2
3
4
5
6
7
8
9
10
11
12
13
14 | 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') -- violates also G-1050
where employee_id = co_employee_id;
end set_dob;
end employee_api;
/
|
Example (good)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 | 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' -- NOSONAR: G-1050 must be a literal
)
where employee_id = co_employee_id;
end set_dob;
end employee_api;
/
|