Oracle Virtual Columns

Oracle Virtual Columns appears to be normal table columns but their values are derived from existing column data rather that storing the data on the disc. It was first introduced with the release Oracle Database 11g Release 1. Following is the syntax to create Oracle Virtual Column.

Column [datatype] [GENERATED ALWAYS] as (expression) [Virtual]

• datatype: Datatype is optional and can be define to make the table datatype.
• GENERATED ALWAYS: This keyword is optional to make table defination more readable.
• Expression: Expression should be defined and enclosed by small bracket.
• VIRTUAL: Virtual is optional keyword that makes table defination more readable.

Following are few restrictions on virtual columns:

1. Indexes defined against virtual column is same as the functional based index.
2. Virtual columns can be used on where clause but we can not manipulate them by any DML.
3. Tables containing virtual column can still be eligible for result caching.
4. Functions in expression must be deterministic at the time of table creating.
5. Virtual columns are not supported for IOT, external, object, cluster or temporary tables.

Following are the restrictions on virtual column expression:

1. It can not refer to another virtual column.
2. It can only refer to columns on the same table.
3. If it refers to a deterministic user-defined function, it cannot be used as a partitioning key column.
4. The output of the expression must be scalar value. It cannot return and Oracle supplied datatype, a user-defined tyoe, or LOB ro LONG RAW.

Creating table with Oracle Virtual Column

CREATE TABLE tbl_advisory_rpt(
owner_name VARCHAR2(32),
tbl_name VARCHAR2(32),
adv_msg VARCHAR2(256),
allocated_space NUMBER AS
(SubStr(adv_msg,InStr(adv_msg,':',1,1)+1,InStr(adv_msg,':',1,2) - InStr(adv_msg,':',1,1) -1 )),
used_space generated always AS
(SubStr(adv_msg,InStr(adv_msg,':',1,3)+1,InStr(adv_msg,':',1,4) - InStr(adv_msg,':',1,3) -1 )) VIRTUAL,
reclaimable_space NUMBER generated always
AS (SubStr(adv_msg,InStr(adv_msg,':',1,5)+1,InStr(adv_msg,':',1,6) - InStr(adv_msg,':',1,5) -1 )) VIRTUAL
);

 

 

Inserting data into the table.

insert into tbl_advisory_rpt(owner_name,tbl_name,adv_msg) values
('SCOTT','EMPLOYEE','Allocated Space:32471289347: Used space:32471200000: Reclaimable Space:89347:');
insert into tbl_advisory_rpt(owner_name,tbl_name,adv_msg) values
('SCOTT','SAL','Allocated Space:4194304: Used space:324712: Reclaimable Space:3869592:');

 

 Selecting data from the table.

select * from tbl_advisory_rpt;

Read More