G-3160: Avoid visible virtual columns.

Major

Maintainability, Reliability

Restriction

ORACLE 12c

Reason

In contrast to visible columns, invisible columns are not part of a record defined using %rowtype construct. This is helpful as a virtual column may not be programmatically populated. If your virtual column is visible you have to manually define the record types used in API packages to be able to exclude them from being part of the record definition.

Invisible columns may be accessed by explicitly adding them to the column list in a SELECT statement.

Example (bad)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
alter table employee
   add total_salary generated always as
       (salary + nvl(commission_pct,0) * salary)
/

declare
   r_employee employee%rowtype;
   l_id employee.employee_id%type := 107;
begin
   r_employee := employee_api.employee_by_id(l_id);
   r_employee.salary := r_employee.salary * constants.small_increase();

   update employee
      set row = r_employee
    where employee_id = l_id;
end;
/

Error report -
ORA-54017: UPDATE operation disallowed ON virtual COLUMNS
ORA-06512: at line 9

Example (good)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
alter table employee
   add total_salary invisible generated always as 
      (salary + nvl(commission_pct,0) * salary)
/

declare
   r_employee employee%rowtype;
   k_id constant employee.employee_id%type := 107;
begin
   r_employee := employee_api.employee_by_id(k_id);
   r_employee.salary := r_employee.salary * constants.small_increase();

   update employee
      set row = r_employee
    where employee_id = k_id;
end;
/