Virtual Columns in Oracle 11g

Most of us have worked with columns that somehow are based on other columns in the same table. For example full_name column which is a concatenation of two columns first_name and last name. Maybe someone will say OK I’ve never done such a work, I used to use views to do it, or maybe I do the concatenation in the query itself without any need to store it and avoid redundancy. I’ll say it’s good but you need to make this every query and every time you need this ?!...

Other that first_name and last_name, there are a lot of more complicated examples which bring pain to us.

Anyway In Oracle 11g, you don’t need to take care of this anymore because Oracle will take care for you. Virtual columns in Oracle 11g is a perfect solution to our past case.

So if we need to create a full_name column, all we have to do is add a virtual column:

CREATE TABLE emp (
    empno      number,
    first_name varchar2(15),
    last_name  varchar2(15),
    full_name  varchar2(30) generated always AS first_name || last_name virtual
);

Then you can consider the virtual column as normal column with few exception like you can’t manipulate it by DML –crystal clear!-, and virtual columns can’t be based on other virtual columns….