Sunday, September 20, 2020
dbametrix
More
    Home Oracle DBA Tips Virtual columns in Oracle

    Virtual columns in Oracle

    The blog post provides information about Virtual columns in Oracle, about how to create and what are the usage in Oracle 11g onward version.

    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 -
    dbametrix

    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.

    - Advertisement -
    dbametrix

    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.

    Consider Reading to these articles:

    - Advertisement -
    dbametrix

    LEAVE A REPLY

    Please enter your comment!
    Please enter your name here

    - Advertisment -
    dbametrix

    Most Popular

    ORA-01194: file 1 needs more recovery to be consistent

    The blog post explains how to restore and recover database using until cancel with error ORA-01194

    How to enable Archivelog

    This blog post explains how to enable archive log mode in Oracle database for newest versions.

    Shared vs Static Library Performance

    The article explains the benefits of a shared library and static library usage in application building for improving application performance.

    Migration methods of Oracle Database

    Blog post explains which tricks and methods are simple to perform migration of small and large Oracle database

    Recent Comments