{"id":203,"date":"2007-11-21T20:27:36","date_gmt":"2007-11-21T19:27:36","guid":{"rendered":"http:\/\/bar-solutions.com\/wordpress\/?p=203"},"modified":"2007-11-21T20:27:36","modified_gmt":"2007-11-21T19:27:36","slug":"oracle-11g-virtual-columns","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=203","title":{"rendered":"Oracle 11G – Virtual Columns"},"content":{"rendered":"

As written before<\/a>, I am currently trying out some things on Oracle 11G after a presentation by Lucas and Alex of Amis<\/a>. 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.<\/p>\n

<\/p>\n

Here’s how to create a virtual column:<\/p>\n

\n
column [datatype] [GENERATED ALWAYS] AS <\/span>(<\/span>column_expression<\/span>)<\/span>\n   [VIRTUAL]\n   [ inline_constraint [inline_constraint]<\/span>...<\/span> ]<\/span><\/div><\/pre>\n
<\/div>\n
for example<\/p>\n
\n
ALTER<\/span> <\/span>TABLE<\/span> T_VIRTUALCOLUMNTEST <\/span>ADD<\/span> (income_1 <\/span>AS<\/span> (salary <\/span>+<\/span> nvl(commission_1,<\/span>0<\/span>)));<\/span><\/div><\/pre>\n
<\/div>\n
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.<\/p>\n
\n
ORA-<\/span>54012<\/span>: virtual column is referenced in a column expression<\/span><\/div><\/pre>\n
<\/div>\n
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.<\/p>\n
An article<\/a> on Oracle Base<\/a> wrote down the follow cotcha’s (amongst others):<\/p>\n