Create Virtual Column in Oracle 11g Table

create table sales

  (

       sales_id      number,

       cust_id       number,

       sales_amt     number,

       sale_category varchar2(6)

       generated always as

     (

          case

            when sales_amt <= 10000 then 'LOW'

           when sales_amt > 10000 and sales_amt <= 100000 then 'MEDIUM'

          when sales_amt > 100000 and sales_amt <= 1000000 then 'HIGH'

            else 'ULTRA'

        end

     ) virtual,

           sale_category1 varchar2(6)

       generated always as

     (

          case

            when sales_amt = 10000 then 'LOW'

            else 'ULTRA'

        end

     ) virtual

  );

INSERT RECORDS 

  insert into sales (sales_id, cust_id, sales_amt) values (1,1,100);

  

  insert into sales (sales_id, cust_id, sales_amt) values (2,102,1500);

  

  insert into sales (sales_id, cust_id, sales_amt) values (3,102,100000);

OUTPUT :

SALES_ID	CUST_ID	SALES_AMT	SALE_CATEGORY	SALE_CATEGORY1
1	1	100	LOW	ULTRA
2	102	1,500	LOW	ULTRA
3	102	100,000	MEDIUM	ULTRA

table properties

VIRTUAL_COLUMN	COLUMN_NAME	COLUMN_ID
NO	SALES_ID	1
NO	CUST_ID	2
NO	SALES_AMT	3
YES	SALE_CATEGORY	4
YES	SALE_CATEGORY1	5