G-8310

🆓

Warning

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

Pattern • Check

Rule Details

Severity Level: Minor
Quality Area: Maintainability
Fixing times: 5 mins
Local-medium
Database Support: OracleDB (v7.3.4 +)

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
nchar
number
numeric
nvarchar2
varchar2
%type

Example

Non-Compliant Example

create or replace package body department_api is
   function dept_by_name( -- @dbLinter ignore(G-7460) non-deterministic function
      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;
/

Issues

Line	Column	Message
3	7	Input size of parameter in_dept_name not validated.

Compliant Solution - ★★★★★

create or replace package body department_api is
   function dept_by_name( -- @dbLinter ignore(G-7460) non-deterministic function
      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:

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;
/

References

same as Trivadis G-8310