Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Basics / Oracle Virtual Column

Oracle Virtual Column

Summary: in this tutorial, you will learn about Oracle virtual columns and how to use them in your database tables.

Introduction to the Oracle virtual column

A virtual column is a table column whose values are calculated automatically using other column values, or another deterministic expression.

Here is the syntax of a virtual column:

column_name [data_type] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

In this syntax:

  • First, specify the name ( column_name) of the virtual column.
  • Second, specify the virtual column’s data type. If you omit the data type, the virtual column will take the data type of the result of the expression.
  • Third, specify an expression in parentheses after the AS keyword. The values of the virtual column will derive from the expression.

Note that the GENERATED ALWAYS and VIRTUAL keywords are for clarity only.

This statement shows how to define a virtual column in the CREATE TABLE statement:

CREATE TABLE table_name ( ..., virtual_column_name AS (expression) );

And this statement illustrates how to add a virtual column to an existing table using the ALTER TABLE statement:

ALTER TABLE table_name ADD ( virtual_column_name AS (expression) );

Oracle virtual column examples

Let’s take some examples of using virtual columns.

1) Creating a table with a virtual column example

First, create a table named parts which has a virtual column:

CREATE TABLE parts( part_id INT GENERATED ALWAYS AS IDENTITY, part_name VARCHAR2(50) NOT NULL, capacity INT NOT NULL, cost DEC(15,2) NOT NULL, list_price DEC(15,2) NOT NULL, gross_margin AS ((list_price - cost) / cost), PRIMARY KEY(part_id) );

In this parts table, the gross_margin column is the virtual column whose values are derived from the list price and cost columns.

Second, insert some rows into the parts table:

INSERT INTO parts(part_name, capacity, cost, list_price) VALUES('G.SKILL TridentZ RGB Series 16GB (2 x 8GB)', 16, 95,105); INSERT INTO parts(part_name, capacity, cost, list_price) VALUES('G.SKILL TridentZ RGB Series 32GB (4x8GB)', 32, 205,220); INSERT INTO parts(part_name, capacity, cost, list_price) VALUES('G.SKILL TridentZ RGB Series 16GB (1 x 8GB)', 8, 50,70);

Third, query data from the parts table:

SELECT * FROM parts;

Here is the output:

oracle virtual column example

As you can see clearly from the output, the gross_margin virtual column has values calculated automatically from the values in the cost and list_price columns.

2) Adding a virtual column to an existing table example

First, add a new column named capacity_description to the parts table using the ALTER TABLE column:

ALTER TABLE parts ADD ( capacity_description AS ( CASE WHEN capacity <= 8 THEN 'Small' WHEN capacity > 8 AND capacity <= 16 THEN 'Medium' WHEN capacity > 16 THEN 'Large' END) )

The value of the capacity_description column is derived from the CASE expression.

Second, query data from the parts table:

SELECT * FROM parts;
oracle virtual column - add new column

Advantages and disadvantages of virtual columns

Virtual columns provide the following advantages:

  • Virtual columns consume minimal space. Oracle only stores the metadata, not the data of the virtual columns.
  • Virtual columns ensure the values are always in sync with the source columns. For example, if you have a date column as the normal column and have the month, quarter, and year columns as the virtual columns. The values in the month, quarter, and year are always in sync with the date column.
  • Virtual columns help avoid using views to display derived values from other columns.

The disadvantage of virtual columns is:

  • Virtual columns may reduce query performance because their values are calculated at run-time.

Virtual column limitations

These are limitations of virtual columns:

  • Virtual columns are only supported in relational heap tables, but not in index-organized, external, object, cluster, or temporary tables.
  • The virtual column cannot be an Oracle-supplied datatype, a user-defined type, or LOB or LONG RAW.

The expression in the virtual column has the following restrictions:

  • It cannot refer to other virtual columns.
  • It cannot refer to normal columns of other tables.
  • It must return a scalar value.
  • It may refer to a deterministic user-defined function, however, if it does, the virtual column cannot be used as a partitioning key column.

Show virtual columns of a table

To show virtual columns of a table, you query from the all_tab_cols view:

SELECT column_name, virtual_column, data_default FROM all_tab_cols WHERE owner = '<owner_name>' AND table_name = '<table_name>';

If the value is the virtual_column is YES, it means that the corresponding column is a virtual column. Otherwise, it is a normal column.

The following statement lists all columns of the parts table, including the virtual columns:

SELECT column_name, virtual_column, data_default FROM all_tab_cols WHERE owner = 'OT' AND table_name = 'PARTS';
oracle virtual column - check virtual columns

In this tutorial, you have learned about the Oracle virtual columns and how to use them in database tables.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle ALTER TABLE ADD Column By Examples
Next Oracle ALTER TABLE MODIFY Column

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.