Virtual Columns-Oracle 11g feature

Learn how to create a table with virtual column with Example Program.

Before learning about virtual column let’s find the meaning of virtual

Meaning of virtual is something that you can view and you cannot feel the touch.

Technical meaning of virtual is a logical thing that do not have physical existence.

Virtual column is a column that can be created for a table without having storage space in DB.

Below is the syntax for virtual column creation

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

[GENERATED ALWAYS] [VIRTUAL] are optional and provided for clarity only.

if [datatype] is not given, then data type is determined based on the result of the expression.

Below is the example for creating a table with virtual column by using above mentioned syntax.

Create table with Virtual Column

CREATE TABLE EMPLOYEE_TBL
(
	EMP_ID		NUMBER,
	FIRST_NAME      VARCHAR2(50),
	LAST_NAME	VARCHAR2(50),
	SALARY		NUMBER(10,2),
	BONUS          	NUMBER(10,2),
	TOTAL_SALARY    NUMBER(10,2) GENERATED ALWAYS AS (SALARY*12 + bonus) VIRTUAL
);


CREATE TABLE EMPLOYEE_TBL
(
  EMP_ID    NUMBER,
  FIRST_NAME      VARCHAR2(50),
  LAST_NAME  VARCHAR2(50),
  SALARY    NUMBER(10,2),
  BONUS            NUMBER(10,2),
  TOTAL_SALARY     AS (SALARY*12 + bonus) 
);

In the above example TOTAL_SALARY is a virtual column that appear to be normal table columns, but their values are derived from the expression when queried.

Note:
derived expression values will not store in the database.only you can view the data when queried.

Insertion script for the above EMPLOYEE_TBL table

here we have populated the table columns with some values except the virtual column.

insert into EMPLOYEE_TBL
  (EMP_ID, FIRST_NAME, LAST_NAME, SALARY, BONUS)
values
  (1348, 'VISWANATH', 'ANNANGI', 50000.00, 3500.00);

insert into EMPLOYEE_TBL
  (EMP_ID, FIRST_NAME, LAST_NAME, SALARY, BONUS)
values
  (1349, 'VINITH', 'PAITHARI', 30000.00, 2500.00);

insert into EMPLOYEE_TBL
  (EMP_ID, FIRST_NAME, LAST_NAME, SALARY, BONUS)
values
  (1350, 'RAVI', 'KANNEGUNDLA', 45000.00, 3500.00);

insert into EMPLOYEE_TBL
  (EMP_ID, FIRST_NAME, LAST_NAME, SALARY, BONUS)
values
  (1351, 'SUDHARMA', 'THUDUMULADENNA', 150000.00, 5000.00);

SELECT * FROM EMPLOYEE_TBL

PL/SQL Developer Export

upon selecting the data using above select query we get the values of TOTAL_SALARY.

Note:

TOTAL_SALARY column results the data based on the expression.
TOTAL_SALARY data is not actually stored in the database but evaluated dynamically(based on the expression defined for virtual column)

Lets try to populate the table columns with some values along with the virtual column.

insert into EMPLOYEE_TBL
  (EMP_ID, FIRST_NAME, LAST_NAME, SALARY, BONUS, TOTAL_SALARY)
values
  (1348, 'VISWANATH', 'ANNANGI', 50000.00, 3500.00, 603500);

insert into EMPLOYEE_TBL
  (EMP_ID, FIRST_NAME, LAST_NAME, SALARY, BONUS, TOTAL_SALARY)
values
  (1349, 'VINITH', 'PAITHARI', 30000.00, 2500.00, 362500);

insert into EMPLOYEE_TBL
  (EMP_ID, FIRST_NAME, LAST_NAME, SALARY, BONUS, TOTAL_SALARY)
values
  (1350, 'RAVI', 'KANNEGUNDLA', 45000.00, 3500.00, 543500);

insert into EMPLOYEE_TBL
  (EMP_ID, FIRST_NAME, LAST_NAME, SALARY, BONUS, TOTAL_SALARY)
values
  (1351, 'SUDHARMA', 'THUDUMULADENNA', 150000.00, 5000.00, 1805000);

Output
ORA-54013: INSERT operation disallowed on virtual columns

Lets try updating the value of virtual Column

UPDATE EMPLOYEE_TBL 
  SET TOTAL_SALARY = 80000;

O/P
ORA-54017: UPDATE operation disallowed on virtual columns

NOTE:
Virtual columns cannot be manipulated by DML statements

UPDATE EMPLOYEE_TBL
SET SALARY=60000 
WHERE TOTAL_SALARY=603500;


DELETE 
FROM EMPLOYEE_TBL
WHERE TOTAL_SALARY=362500

Note:
Virtual columns can be referenced in the WHERE clause of updates and deletes but not be manipulated by DML.