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:

- Advertisement -

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/yourpassword@localhost:1521/TESDB1

Create a user with name: Store.

- Advertisement -

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;
SQL> disc;

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,
BUY_ID,
QUANTITY,
PRICE)
VALUES (200,350,10,20.35);

9. Select the record inserted with the statement:
SELECT *
FROM SALES;

I hope it helps to understand how Virtual columns will be created and what is the usage.

- Advertisement -