G-3160: Avoid visible virtual columns.

Major

Maintainability, Reliability

Unsupported in db* CODECOP Validators

We cannot identify the type of a column. Requires create table and alter table parser support or access to the Oracle Data Dictionary.

Restriction

Oracle Database 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)

alter table employees
   add total_salary generated always as (salary + nvl(commission_pct,0) * salary)
/

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

   update employees
      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)

alter table employees
   add total_salary invisible generated always as
      (salary + nvl(commission_pct,0) * salary)
/

declare
   r_employee employees%rowtype;
   co_id      constant employees.employee_id%type := 107;
begin
   r_employee        := employee_api.employee_by_id(co_id);
   r_employee.salary := r_employee.salary * constants_up.small_increase();

   update employees
      set row = r_employee
    where employee_id = co_id;
end;
/