Add a Virtual Column to an Existing Table in Oracle

If we need to add a virtual column to an existing table in Oracle Database, we can do so with the usual SQL ALTER TABLE statement.

Example

Suppose we have the following table:

SELECT * FROM Products;

Result:

ID	NAME	QTY	PRICE
1	Hammer	10	10
2	Saw	5	12
3	Wrench	7	42
4	Chisel	9	25
5	Bandage	70	15

And let’s say we want to add a virtual column that calculates the total price of all stock for each item. In other words, we want a virtual column that multiplies the Qty column by the Price column.

In that case, we could do this:

ALTER TABLE Products 
    ADD (TotalValue INT GENERATED ALWAYS AS (Qty * Price) VIRTUAL);

That adds a virtual column called TotalValue with the specifications as described.

Now let’s query the table again:

SELECT * FROM Products;

Result:

ID	NAME	QTY	PRICE	TOTALVALUE
1	Hammer	10	10	100
2	Saw	5	12	60
3	Wrench	7	42	294
4	Chisel	9	25	225
5	Bandage	70	15	1050

This time we see the virtual column and its computed values.

Optional Keywords

The GENERATED ALWAYS and VIRTUAL keywords are optional.

So we can shorten the above example to this:

ALTER TABLE Products 
    ADD (TotalValue INT GENERATED ALWAYS AS (Qty * Price));

Or even this:

ALTER TABLE Products 
    ADD (TotalValue INT AS (Qty * Price));

In Oracle Database, all generated columns are virtual (in other DBMSs, generated columns can be either stored or virtual), and so specifying VIRTUAL is just being explicit. It’s a similar thing with GENERATED ALWAYS. By specifying AS followed by the expression, we’re implying that it’s a virtual/generated column.