Saturday, May 5, 2012

Virtual column (computed column) in Oracle DB

create table c ( a varchar2(10), b as (substr( a, 5, 2 )));
insert into c (a) values ('TOPOLINO');
insert into c (a) values ('PAPERINO');

select * from c;

"A" "B"

I think this valuable feature was introduced only recently...

A noticeable case is:
b as ( 'string1' || a || 'string2)

Incidentally the feature is not supported in SQL Developer UI, so once you use virtual columns you must alter the table manually with DDL commands.

No comments: