Monday, November 23, 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
    - Advertisment -
    dbametrix

    Most Popular

    Oracle Scheduler Jobs Example

    Blog post explains how to configure job scheduler in Oracle by example with how to manage, delete, drop, disable and enable jobs with how to check history of job if broken and enable.

    Identifying important information in Big Data

    The way in which Big Data technologies have evolved in the real word enterprise goes on to show that even technologists and scientists who might have disparaged the word (Big Data) previously

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    Recent Comments