Skip to content

G-2110: Try to use anchored declarations for variables, constants and types.

Major

Maintainability, Reliability

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
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;
/

Example (good)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
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;
/