0VIRTUAL OR DERIVED COLUMN
A nice feature introduced in Oracle 11gR1. Welcome to the practical analysis of various scenarios with virtual columns - introduced in 11gR1 are like normal table columns whose values are derived from other columns.
- Creating table with virtual column
DROP TABLE t_virtual_test1 PURGE;
CREATE TABLE t_virtual_test1
(
salary NUMBER,
commission NUMBER,
total NUMBER GENERATED ALWAYS AS (salary + commission) VIRTUAL
);
- Inserting values into virtual columns
INSERT INTO t_virtual_test1 (salary, commission) VALUES (10000,999);
COMMIT;
The third column (which is virtual/derived) is auto derived (not auto populated).
SELECT * FROM t_virtual_test1;
| SALARY | COMMISSION | TOTAL |
| 10000 | 999 | 10999 |
We will see difference between auto derivation and auto population in later sections. Actually, the above logic is same as deriving values in SELECT like
SELECT salary, commission, salary + commission total FROM t_virtual_test1;
We will try out different scenarios starting with the below INSERT. Any guess about the value in the virtual column TOTAL?
INSERT INTO t_virtual_test1 (salary, commission) VALUES (20000,NULL);
COMMIT;
SELECT * FROM t_virtual_test1;
| SALARY | COMMISSION | TOTAL |
| Rs. 10000 | null | null |
- Data dictionary view
As a developer/DBA, it is important to get to know the data dictionary details corresponding to each new concept your are learning. For virtual columns, we can check the column DATA_DEFAULT in data dictionary USER_TAB_COLUMNS
| TABLE_NAME | COLUMN_NAME | DATA_TYPE | DATA_DEFAULT |
| T_VIRTUAL_TEST1 | SALARY | NUMBER | null |
| T_VIRTUAL_TEST1 | COMMISSION | NUMBER | null |
| T_VIRTUAL_TEST1 | TOTAL | NUMBER | "SALARY"+"COMMISSION" |
- Other scenarios
* Try updating the actual columns, and when you SELECT, the virtual column value will be derived based on the new/updated/latest values in the actual columns.
UPDATE t_virtual_test1
SET commission = 111;
COMMIT;
SELECT * FROM t_virtual_test1;
* Try giving your own value for derived column. You cannot insert value for derived columns because - it is always derived.
INSERT INTO t_virtual_test1 (salary, commission, total) VALUES (10000,999,10999);
Read the error report carefully - you get so much info/clue from this report
always note the error code and error message
--ORA-54013:Insert operation disallowed on virtual columns
Same for UPDATE.
UPDATE t_virtual_test1 SET total = 4444 WHERE salary = 10000;
error - Update operation disallowed on virtual columns
* Is there any way to include derived column name in INSERT?
yes - we can use the DEFAULT keyword (DEFAULT - is a basic concept which we normally use in our DMLs to instruct Oracle to go by default settings given during table creation. Please search and learn more about default).
INSERT INTO t_virtual_test1 (salary, commission, total) VALUES (11,22,DEFAULT);
COMMIT;
Note: Using DEFAULT in UPDATE will not work for virtual columns
- Variations in virtual column creation Syntax
Above points discussed gives an overview of Virtual column. Now we will look into the optional syntax keywords and what will happen if we ignore those keywords while virtual column creation
* We can omit the datatype of virtual column - datatype is auto set based on the expression given, like (salary + commission) gives a NUMBER in below example
DROP TABLE t_virtual_test2 PURGE;
CREATE TABLE t_virtual_test2
(
salary NUMBER,
commission NUMBER,
total GENERATED ALWAYS AS (salary + commission) VIRTUAL
);
DESC t_virtual_test2
SALARY NUMBER
COMMISSION NUMBER
TOTAL NUMBER
* GENERATED ALWAYS and VIRTUAL keywords are optional
DROP TABLE t_virtual_test3 PURGE;
CREATE TABLE t_virtual_test3
(
salary NUMBER,
commission NUMBER,
total AS (salary + commission)
);
INSERT INTO t_virtual_Test3 VALUES (10000,300,default);
COMMIT;
Keep you updated in next post.
I keep my posts designed in such a way that the contents are more program oriented. The program speaks volumes. I welcome your suggestions and reviews to update my style.
