G-2110

🆓

Warning

Try to use anchored declarations for variables, constants and types.

Rule Details

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

Reason

Changing the size of the database column last_name in the employees table from varchar2(20) to varchar2(30) will result in an error within your code whenever a value larger than the hard-coded size is read from the table. This can be avoided using anchored declarations.

Example

Non-Compliant Example

create or replace package body my_package is
   procedure my_proc is
      l_last_name  varchar2(20 char);
      co_first_row constant integer := 1;
   begin
      select e.last_name
        into l_last_name
        from employees e
       where rownum = co_first_row;
   exception
      when no_data_found then
         null; -- handle no_data_found
      when too_many_rows then
         null; -- handle too_many_rows (impossible)
   end my_proc;
end my_package;
/

Issues

Line	Column	Message
3	20	Hard-coded data type of l_last_name.

Compliant Solution - ★★★★★

create or replace package body my_package is
   procedure my_proc is
      l_last_name  employees.last_name%type;
      co_first_row constant integer := 1;
   begin
      select e.last_name
        into l_last_name
        from employees e
       where rownum = co_first_row;
   exception
      when no_data_found then
         null; -- handle no_data_found
      when too_many_rows then
         null; -- handle too_many_rows (impossible)
   end my_proc;
end my_package;
/

References

same as Trivadis G-2110