Virtual column in Oracle

Create a table with Virtual Column in Oracle

Virtual column is the column in the table in which the value is derived from an expression or based on calculating from other columns


CREATE TABLE table_name (
    col_name1 datatype,
    col_name2 datatype
    virtual_column_name AS (expression)


CREATE TABLE employees(
    id INT,
    employee_name VARCHAR2(50) NOT NULL,
    rollno INT NOT NULL,
    fee DEC(15,2) NOT NULL,
    tax DEC(15,2) NOT NULL,
    total_fee AS ((fee + ((fee*tax)/100)),

You can also add a Virtual column later by ALTER command in Oracle:

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

We can also use case statements on the Virtual column like:

ALTER TABLE students
    Grade_Marks AS (
                WHEN Score between 90 and 100 THEN 'A+ Grade' 
                WHEN score between 75 and 89 THEN 'A Grade'
                WHEN score between 74 and 60 THEN 'B Grade'

Check table has virtual columns

select column_id, column_name, virtual_column, data_default from dba_tab_cols;

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.