Oracle 11G – Virtual Columns

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]... ]

for example

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.

An article on Oracle Base wrote down the follow cotcha’s (amongst others):

  • 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:

CREATE TABLE Documentation
Virtual Columns in Oracle Database 11g Release 1
Understanding Virtual Columns

Leave a Reply

Your email address will not be published.