On 13th October 2007, I wrote about “Understanding Virtual Columns“.

Then Ray DeBruyn ask me a very good question, which I totally miss to ask myself when I was writing about it.

What happens if I use:
INSERT INTO myTable VALUES myTableRec
or
UPDATE myTable SET ROW = myTableRec

Assuming I declare myTableRec as myTable%ROWTYPE, is the new feature smart enough to ignore the virtual column?

To find out the answer, I did following:

SQL> create table virtual_col(
2 a number,
3 b number,
4 c number GENERATED ALWAYS AS (a+b) VIRTUAL,
5 d number);

Table created.

SQL> desc virtual_col;
Name Null? Type
—————————————– ——– —————————-

A NUMBER
B NUMBER
C NUMBER
D NUMBER

SQL> insert into virtual_col(1,2,3);
insert into virtual_col(1,2,3)
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into virtual_col values (1,2,3);
insert into virtual_col values (1,2,3)
*
ERROR at line 1:
ORA-00947: not enough values

SQL> insert into virtual_col values (1,2,3,4);
insert into virtual_col values (1,2,3,4)
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

SQL> insert into virtual_col(a,b,d) values (1,2,4);

1 row created.

SQL> declare
2 l_rec virtual_col%ROWTYPE;
3 begin
4 UPDATE virtual_col SET ROW = l_rec;
5 end;
6 /
declare
*
ERROR at line 1:
ORA-54017: UPDATE operation disallowed on virtual columns
ORA-06512: at line 4

To my surprise, I found out that oracle 11g is not smart enough to ignore the virtual columns and it gives error when we want to do DML operation rowwise.

We need to mention each columns and its value explicitly to do update or insert.