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