# G-3160: Avoid visible virtual columns.

Major

Maintainability, Reliability

Unsupported in PL/SQL Cop Validators

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

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.

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