Virtual Columns

Oracle 11g allows you to create virtual columns in a table. Values are not stored for these columns, Oracle calculates them at runtime. You can see the expression used to generate the value in the DATA_DEFAULT column of the USER_TAB_COLUMNS view.

As you might expect, you cannot UPDATE virtual columns directly. If you try, you get an ORA-54017.

 

It isn’t quite so obvious but you cannot use a virtual column to generate the value of another virtual column either. If you try, you get an ORA-54012: 

 

SQL> create table tab1

  2  (col1 number,

  3   col2 number,

  4   col3 number generated always

  5   as (col1 + col2) virtual)

  6  /

 

Table created.

 

SQL> insert into tab1(col1,col2) values(1,2)

  2  /

 

1 row created.

 

SQL> select * from tab1

  2  /

 

      COL1       COL2       COL3

---------- ---------- ----------

         1          2          3

 

SQL> column column_name format a15

SQL> column data_default format a15

SQL> select column_name, data_default

  2  from user_tab_columns

  3  where table_name = 'TAB1'

  4  /

 

COLUMN_NAME     DATA_DEFAULT

--------------- ---------------

COL1

COL2

COL3            "COL1"+"COL2"

 

SQL> update tab1 set col3 = 4

  2  /

update tab1 set col3 = 4

       *

ERROR at line 1:

ORA-54017: UPDATE operation disallowed on virtual

columns

 

SQL> drop table tab1

  2  /

 

Table dropped.

 

SQL> create table tab1

  2  (col1 number,

  3   col2 number,

  4   col3 number generated always

  5   as (col1 + col2) virtual,

  6   col4 number generated always

  7   as (col1 + col3) virtual)

  8  /

col3 number generated always

*

ERROR at line 4:

ORA-54012: virtual column is referenced in a column

expression

 

SQL>