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

Minor

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.

Example (bad)

CREATE OR REPLACE PACKAGE BODY department_api IS
   FUNCTION dept_by_name (in_dept_name IN departments.department_name%TYPE)
      RETURN departments%ROWTYPE IS
      l_return departments%rowtype;
   BEGIN
       IF    in_dept_name IS NULL 
          OR LENGTH(in_dept_name) > 20
       THEN
          RAISE err.e_param_to_large;
       END IF;
       -- get the department by name
       SELECT *
         FROM departments
        WHERE department_name = in_dept_name;

       RETURN l_return;
   END dept_by_name;
END department_api;
/

Example (good)

CREATE OR REPLACE PACKAGE BODY department_api IS
   FUNCTION dept_by_name (in_dept_name IN departments.department_name%TYPE)
      RETURN departments%ROWTYPE IS
      l_dept_name departments.department_name%TYPE NOT NULL := in_dept_name;
      l_return departments%rowtype;
   BEGIN
       -- get the department by name
       SELECT *
         FROM departments
        WHERE department_name = l_dept_name;

       RETURN l_return;
   END dept_by_name;
END department_api;
/

Function call

...
   r_deparment := department_api.dept_by_name('Far to long name of a department');
...
EXCEPTION
   WHEN VALUE_ERROR THEN ...