G-5060: Avoid unhandled exceptions.

Major

Reliability

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 (bad)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
create or replace package body department_api is
   function name_by_id (in_id in department.department_id%type) 
      return department.department_name%type is
      l_department_name department.department_name%type;
   begin
      select department_name
        into l_department_name
        from department
       where department_id = in_id;

      return l_department_name;   
   end name_by_id;
end department_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 department_api is
   function name_by_id (in_id in department.department_id%type) 
      return department.department_name%type is
      l_department_name department.department_name%type;
   begin
      select department_name
        into l_department_name
        from department
       where department_id = in_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;
/