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)

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