G-3160

🆓

Error

Avoid visible virtual columns.

Rule Details

Severity Level: Blocker
Quality Area: Reliability
Fixing times: 60 mins
Other
Database Support: OracleDB (v12.1.0 +)

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

Non-Compliant Example

alter table employees
   add total_salary 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(l_id);
   r_employee.salary := r_employee.salary * constants_up.small_increase();

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

Issues

Line	Column	Message
7	15	Virtual column total_salary is visible.

Explanation

Error report -
ORA-54017: update operation disallowed on virtual columns
ORA-06512: at line 9

Compliant Solution - ★★★★★

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

Tests

Test SQL query

select owner || '.' || table_name || '.' || column_name as identifier,
       'Virtual column ' || column_name || ' in table ' || owner || '.' || table_name || ' is visible' as message
  from all_tab_cols
 where virtual_column = 'YES'
   and hidden_column = 'NO'
   and owner in (#SchemaNames#)
 order by owner, table_name, column_name

Test results

Identifier	Message	Migration
HR.EMPLOYEES.TOTAL_SALARY	Virtual column TOTAL_SALARY in table HR.EMPLOYEES is visible.	-

Parameters

Use parameters to customize the rule to your needs.

Parameter	Description	Default Value
SchemaNames	Comma separated list of database schemas owning the database objects of an application.	dbl_owner

References

same as Trivadis G-3160