Virtual columns in Oracle 11g onward versions
Virtual columns are one of the new features introduced in Oracle Database 11g and included in Express Edition 11g Version 2 and later. It consists of fields that obtain their values from expressions that refer to other columns, such expressions can include calls to functions.
A concrete example:
We will create a table that records the sale of products by adding the following fields:
purchase_id, product_id, price, quantity, amount.
Run the SQL command line.
Login as SYSTEM user:
conn system/[email protected]:1521/TESDB1
Create a user with name: Store.
CREATE USER store IDENTIFIED BY abc123 DEFAULT TABLESPACE USERS;
4. Grant CONNECT and RESOURCE roles.
GRANT CONNECT, RESOURCE TO store;
5. Disconnect with the disc command;
6. Login as a store user.
CONN store/abc123 @ localhost: 1521 / XEPDB1
7. Enter the following sentence:
CREATE TABLE SALES (
PRODUCT_ID NUMBER (6) NOT NULL,
BUY_ID NUMBER (5) NOT NULL,
QUANTITY NUMBER (7,3) NOT NULL,
PRICE NUMBER (7,3) NOT NULL,
AMOUNT NUMBER GENERATED ALWAYS AS
(QUANTITY * PRICE) VIRTUAL
8. Insert a test record.
INSERT INTO SALES (PRODUCT_ID,
9. Select the record inserted with the statement:
I hope it helps to understand how Virtual columns will be created and what is the usage.
Consider Reading to these articles: