Virtual Column in Oracle

Notes:
1. Indexes can be defined on virtual column., and these are equivalent to function based index.
2. virtual columns can be referred any in select stateless such as "where" clause etc, however DMLs are not allowed on virtual columns.
3. Virtual columns can be defined on "Heap organized table", and not supported for "external", "IOT", clustered or temporary tables.
4. virtual column cannot refer to other virtual column. It should refer to the columns of the same table.
5. The expression for virtual column should evaluate to scalar data type only.
6. Virtual column can be used as partition key.


CREATE TABLE loan_details (
    sno                         number,
    principal_amount  number,
    rate_of_interest     number,
    number_of_years  number
);

insert into loan_details values(1,5000,8,1);
insert into loan_details values(2,5000,10,1);
insert into loan_details values(3,5000,12,1);

commit;

SELECT
    sno,
    principal_amount,
    number_of_years,
    rate_of_interest,
    ( round(principal_amount * number_of_years * rate_of_interest  / 100) ) simple_interest,
    (round(principal_amount * power( (1 + ( (rate_of_interest / 100) / 12) ), (number_of_years * 12) )-principal_amount)) compound_interest
FROM
    loan_details;


drop table loan_details;

CREATE TABLE loan_details (
    sno                        number,
    principal_amount number,
    rate_of_interest    number,
    number_of_years  number,
simple_interest  as ( round(principal_amount * number_of_years * rate_of_interest  / 100) ),
compound_interest NUMBER
GENERATED ALWAYS AS
(round(principal_amount * power( (1 + ( (rate_of_interest / 100) / 12) ), (number_of_years * 12) )-principal_amount))
VIRTUAL
);

insert into loan_details(sno,principal_amount,rate_of_interest,number_of_years) values(1,5000,8,1);
insert into loan_details(sno,principal_amount,rate_of_interest,number_of_years) values(2,5000,10,1);
insert into loan_details(sno,principal_amount,rate_of_interest,number_of_years) values(3,5000,12,1);

select * from loan_details;

desc loan_details;

select * from USER_TAB_COLUMNS where table_name = 'LOAN_DETAILS';