Oracle Database supports the creation of generated columns. A generated column is a column whose value is derived from an expression that computes values from other columns.

In Oracle Database, generated columns are usually referred to as virtual columns. Generated columns can also be referred to as computed columns in other RDBMSs (such as SQL Server). Either way, they do pretty much the same thing – they contain an expression that computes a value based on values in other columns in the same table.

Example

Let’s jump right in and create a table that contains a generated/virtual column:

CREATE TABLE Products( 
    Id INT PRIMARY KEY, 
    Name VARCHAR(255),
    Qty INT,
    Price INT,
    TotalValue INT GENERATED ALWAYS AS (Qty * Price)
);

In this example, the TotalValue column is a virtual column. We can see that it contains an expression (Qty * Price) which is the expression that it will use to compute its value. In this case we used GENERATED ALWAYS to explicitly specify it as a generated column, but this is optional. We could just as easily have used the following:

CREATE TABLE Products( 
    Id INT PRIMARY KEY, 
    Name VARCHAR(255),
    Qty INT,
    Price INT,
    TotalValue INT AS (Qty * Price)
);

The only difference is that we dropped the GENERATED ALWAYS part.

Now that we’re talking about optional keywords, we can optionally include the VIRTUAL keyword:

CREATE TABLE Products( 
    Id INT PRIMARY KEY, 
    Name VARCHAR(255),
    Qty INT,
    Price INT,
    TotalValue INT GENERATED ALWAYS AS (Qty * Price) VIRTUAL
);

Another option is to specify either VISIBLE or INVISIBLE. The default is VISIBLE.

Anyway, let’s insert some data:

INSERT INTO Products(Id,Name,Qty,Price) VALUES(1,'Hammer',10,9.99);
INSERT INTO Products(Id,Name,Qty,Price) VALUES(2,'Saw',5,11.50);
INSERT INTO Products(Id,Name,Qty,Price) VALUES(3,'Wrench',7,42);
INSERT INTO Products(Id,Name,Qty,Price) VALUES(4,'Chisel',9,25);
INSERT INTO Products(Id,Name,Qty,Price) VALUES(5,'Bandage',70,15);

Note that we only inserted data into the other columns. We didn’t insert data into the virtual column. That’s because the virtual column will compute its values based on the values in the Qty and Price columns.

To demonstrate this, let’s select all data from the table:

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

We can see that the values in the TotalValue column are exactly as we’d expect. They’re a result of multiplying the Qty column with the Price column.

Virtual vs Stored Generated Columns

There are two types of generated columns; virtual and stored.

• STORED (aka PERSISTENT): Column values are stored in the table. A stored generated column is computed when it is written (inserted or updated) and occupies storage as if it were a normal column.
• VIRTUAL:  A virtual generated column occupies no storage and is computed when it is read. A virtual column takes no storage.

Some RDBMSs support both types of generated columns, and others support only one. Oracle Database 21c, Oracle Database only supports VIRTUAL generated columns.

More Information

For more specific information about creating virtual columns in Oracle Database, including the full syntax, see the Oracle Database CREATE TABLE documentation.