As written before, I am currently trying out some things on Oracle 11G after a presentation by Lucas and Alex of Amis. One of the new features they talked about was the virtual columns option in Oracle. This a column based on an expression that doesn’t really exist in the database.
Here’s how to create a virtual column:
column [datatype] [GENERATED ALWAYS] AS (column_expression) [VIRTUAL] [ inline_constraint [inline_constraint]... ]
ALTER TABLE T_VIRTUALCOLUMNTEST ADD (income_1 AS (salary + nvl(commission_1,0)));
Of course the same behavior can be achieved using real (normal) columns and triggers, but this approach requires less coding. Of course I tried to create a virtual column based on another virtual column because I wanted to achieve some sort of Single Point of Definition, but that was not possible. I got an error telling me this is not possible.
ORA-54012: virtual column is referenced in a column expression
This can however be resolved by adding a real column which is updated by a trigger. This trigger will be invalidated when the virtual column is dropped from the table, but the value is preserved in the real column. The trigger will be invalid though.
- The expression used in the virtual column definition has the following restrictions:
- It cannot refer to another virtual column by name.
- It can only refer to columns defined in the same table.
- If it refers to a deterministic user-defined function, it cannot be used as a partitioning key column.
- The output of the expression must be a scalar value. It cannot return an Oracle supplied datatype, a user-defined type, or LOB or LONG RAW.
So, I thought, how about using a function for the virtual column.
create or replace function sf_total ( value1_in in NUMBER , value2_in in number) return number deterministic is l_returnvalue number; begin l_returnvalue := value1_in + value2_in; return(l_returnvalue); end sf_total;
But when I tried this, using two virtual columns as parameters I got the same error as mentioned earlier. I guess this is covered by the first restriction. Too bad.
Some useful links about virtual columns: