G-5060
🆓Avoid unhandled exceptions.
Reason
This may be your intention, but you should review the code to confirm this behavior.
If you are raising an error in a program, then you are clearly predicting a situation in which that error will occur. You should consider including a handler in your code for predictable errors, allowing for a graceful and informative failure. After all, it is much more difficult for an enclosing block to be aware of the various errors you might raise and more importantly, what should be done in response to the error.
The form that this failure takes does not necessarily need to be an exception. When writing functions, you may well decide that in the case of certain exceptions, you will want to return a value such as null, rather than allow an exception to propagate out of the function.
Example
Non-Compliant Example
create or replace package body department_api is
function name_by_id(in_id in departments.department_id%type)
return departments.department_name%type is
co_id constant departments.department_id%type := in_id;
l_department_name departments.department_name%type;
begin
select department_name
into l_department_name
from departments
where department_id = co_id;
return l_department_name;
end name_by_id;
end department_api;
/Issues
| Line | Column | Message |
|---|---|---|
| 8 | 9 | Avoid unhandled exceptions. |
Compliant Solution - ★★★★★
create or replace package body department_api is
function name_by_id(in_id in departments.department_id%type)
return departments.department_name%type is
co_id constant departments.department_id%type := in_id;
l_department_name departments.department_name%type;
begin
select department_name
into l_department_name
from departments
where department_id = co_id;
return l_department_name;
exception
when no_data_found then
return null;
when too_many_rows then
raise;
end name_by_id;
end department_api;
/References
- same as Trivadis G-5060