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

Syntax:

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

Example:

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)),
    PRIMARY KEY(id)
);

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
ADD (
    Grade_Marks AS (
            CASE 
                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'
            END)
)

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:

WordPress.com Logo

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

Twitter picture

You are commenting using your Twitter 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.