Skip to content

G-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.

Major

Maintainability, Reliability, Reusability, Testability

Reason

This technique raises an error (value_error) which may not be handled in the called program unit. This is the right way to do it, as the error is not within this unit but when calling it, so the caller should handle the error.

To limit the number of false positives, only the following data types used in parameters should be considered:

  • char
  • dec
  • decimal
  • interval day to second
  • interval year to month
  • nchar
  • number
  • numeric
  • nvarchar2
  • varchar2
  • %type

Example (bad)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
create or replace package body department_api is
   function dept_by_name( -- NOSONAR: non-deterministic
      in_dept_name in departments.department_name%type
   )
      return departments%rowtype is
      r_return                departments%rowtype;
      co_max_dept_name_length constant integer := 20;
   begin
      if in_dept_name is null or length(in_dept_name) > co_max_dept_name_length then
         raise err.e_param_to_large;
      end if;
      -- get the department by name
      <<trap>>
      begin
         select *
           into r_return
           from departments
          where department_name = in_dept_name;
         return r_return;
      exception
         when no_data_found then
            return null;
         when too_many_rows then
            raise;
      end trap;
   end dept_by_name;
end department_api;
/

Example (good)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
create or replace package body department_api is
   function dept_by_name( -- NOSONAR: non-deterministic
      in_dept_name in departments.department_name%type
   )
      return departments%rowtype is
      co_dept_name constant departments.department_name%type not null := in_dept_name;
      r_return     departments%rowtype;
   begin
      -- get the department by name
      <<trap>>
      begin
         select *
           into r_return
           from departments
          where department_name = co_dept_name;
         return r_return;
      exception
         when no_data_found then
            return null;
         when too_many_rows then
            raise;
      end trap;
   end dept_by_name;
end department_api;
/

The exception should be handled where the function is called, like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
declare
   co_dept_name constant type_up.text := 'Far to long name of a department';
begin
   pre_processing;
   r_department := department_api.dept_by_name(co_dept_name);
   post_processing;
exception
   when value_error then
      handle_error;
end;
/