How to Create Virtual Columns in Oracle

A fare with tax, a full name, a season derived from a date: values computed from other columns of the same row are often repeated in every query or stored redundantly. A virtual column defines the expression once, in the table. It takes no space, is computed whenever it is read, and can be indexed and used in constraints and partitioning.

Code for This Guide

The main examples are in the examples/tables folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

column [type] [generated always] as (expr) [virtual]

GENERATED ALWAYS and VIRTUAL are optional words. The expression can use other columns of the same row, but not other tables or non-deterministic functions such as SYSDATE.

Add Virtual Columns

The examples use a FARE_RULES table, created first:

Example:

create table fare_rules (
  rule_id       number        constraint fare_rules_pk primary key,
  route_id      number        not null constraint fare_rules_route_fk references routes,
  cabin         varchar2(8)   default 'ECONOMY' not null,
  valid_from    date          not null,
  valid_to      date,
  base_fare     number(8,2)   not null,
  refundable    boolean       default false,
  notes         varchar2(200),
  constraint fare_rules_cabin_ck check (cabin in ('BUSINESS', 'ECONOMY')),
  constraint fare_rules_dates_ck check (valid_to is null or valid_to > valid_from),
  constraint fare_rules_uk unique (route_id, cabin, valid_from)
);

insert into fare_rules (rule_id, route_id, valid_from, base_fare)
values (1, 1, date '2026-04-01', 520);

select rule_id, route_id, cabin, valid_from, base_fare, refundable from fare_rules;

Output:

Table FARE_RULES created.

1 row inserted.

   RULE_ID    ROUTE_ID CABIN      VALID_FROM        BASE_FARE REFUNDABLE
__________ ___________ __________ ______________ ____________ _____________
         1           1 ECONOMY    01-APR-2026             520 false

Two virtual columns are then added: the fare with 5 percent tax, and a season derived from the start date.

Example:

alter table fare_rules add (
  fare_with_tax number generated always as (round(base_fare * 1.05, 2)) virtual,
  season        varchar2(6) as (case when extract(month from valid_from) in (6, 7, 8)
                                       then 'SUMMER' else 'OTHER' end)
);

select rule_id, base_fare, fare_with_tax, season from fare_rules;

Output:

Table FARE_RULES altered.

   RULE_ID    BASE_FARE    FARE_WITH_TAX SEASON
__________ ____________ ________________ _________
         1          520              546 OTHER

The values appear like any column, though nothing is stored for them.

Index and Protect Them

A virtual column can be indexed, so queries on the computed value use the index. It cannot be written to.

Example:

insert into fare_quotes (quote_id, base_fare) values (1, 400), (2, 900);
create index fare_quotes_tax_ix on fare_quotes (fare_with_tax);
select quote_id, base_fare, fare_with_tax from fare_quotes where fare_with_tax > 500;
insert into fare_quotes (quote_id, base_fare, fare_with_tax) values (3, 100, 105);

Output:

2 rows inserted.

Index FARE_QUOTES_TAX_IX created.

   QUOTE_ID    BASE_FARE    FARE_WITH_TAX
___________ ____________ ________________
          2          900              945

Error starting at line : 4
In command -
insert into fare_quotes (quote_id, base_fare, fare_with_tax) values (3, 100, 105)
Error at Command Line : 4 Column : 47
Error report -
SQL Error: ORA-54013: INSERT operation disallowed on virtual columns

The query filters on fare_with_tax and can use the index on it. Inserting a value into the virtual column fails with ORA-54013. Drop the work table afterward with drop table fare_quotes purge.

Things to Know

• A virtual column can be a partitioning key, which lets you partition by a value derived from other columns.
• CHECK constraints and foreign keys can reference virtual columns.
• Indexing a virtual column has the same effect as a function-based index on its expression, but the expression has a name.

Related Guides

• How to Create Identity Columns in Oracle

Conclusion

Virtual columns compute a value from other columns of the row whenever it is read, using no storage. Define derived values once in the table, index them for fast queries, and use them in constraints and partitioning.