For last few days I was busy packing my things plus doing some important task related to my Australia trip but at last got time to write about something new in Oracle 11g called Virtual Columns.

First lets go through one situation, we have a sales table with Gross_Amount and Discount_Amount.

Now
Net_Amount := Gross_Amount – Discount Amount;

We can tell our guys to use this formula everywhere. Now if formula changes due to some reason, then we will need to change the formula everywhere.

Other way to tackle such situation is to create the separate view (formula embedded in it) and everybody use that view everywhere.

So if formula changes then we just need to alter the view.

Another way is to add a new column called Gross_Amount and calculate it at trigger level, but this will result in wastage of hard disk space.

Now with Oracle 11g, there is a new feature called Virtual Column to handle this situation.

With help of this new feature we can just add one virtual column based on the formula in the existing table and if formula changes then just need to alter this column expression or formula.

Now let me talk about Virtual Column, what exactly it is?

In Oracle 11g, a table can contain a virtual column which derives the values by evaluating a user-specified expressions.The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions.

A virtual column’s does not consume any space on disk as its value is calculated only when it is queried based on its column expression.

It can be defined at the time of table creation or modification.

We can index virtual columns, collect statistics on them, and create integrity constraints. Thus, they can be treated much as normal columns.

Off course, we cannot explicitly write to a virtual column.

As Oracle Documents rightly says:
“This makes application development easier and less error-prone, as well as enhances query optimization by providing additional statistics to the optimizer for these virtual columns”

Syntax is as follows:

CREATE STATMENT:

CREATE TABLE table_name (
column_name data_type
…..
column_name data_type GENERATED ALWAYS AS (column_expression) VIRTUAL — For Virtual Column
) ;

ALTER STATMENT:

ALTER TABLE table_name action (columns datatype GENERATED ALWAYS AS (column_expression) VIRTUAL);

Example : Now lets suppose that we are working in scott schema.

First I created the following function:

CREATE FUNCTION get_grade(p_sal emp.sal%TYPE) RETURN VARCHAR2 DETERMINISTIC IS
l_grade VARCHAR2(6);
BEGIN

l_grade := CASE
WHEN p_sal <= 1000 THEN ’3′
WHEN p_sal <= 5000 THEN ’4′
WHEN p_sal <= 6000 THEN ’5′
WHEN p_sal <= 7000 THEN ’6′
WHEN p_sal <= 8000 THEN ’7′
WHEN p_sal <= 9000 THEN ’8′
WHEN p_sal <= 10000 THEN ’9′
ELSE ’10′
END ;
RETURN l_grade;
END;

Then I added a virtual column as follows to emp table:

ALTER TABLE emp ADD(
sal_grade VARCHAR2(6) GENERATED ALWAYS AS (SUBSTR(get_grade(sal),1,6)) VIRTUAL
);

Just in case you are wondering what will be create statement, it will be as follows:

CREATE TABLE emp (
empno NUMBER (4) NOT NULL,
ename VARCHAR2 (10),
job VARCHAR2 (9),
mgr NUMBER (4),
hiredate DATE,
sal NUMBER (7,2),
comm NUMBER (7,2),
deptno NUMBER (2),
sal_grade VARCHAR2(6) GENERATED ALWAYS AS (SUBSTR(get_grade(sal),1,6)) VIRTUAL,
CONSTRAINT pk_emp PRIMARY KEY ( empno ) ) ;

List of Virtual Column Related Errors:

ORA-12996: cannot drop system-generated virtual column
Cause: An attempt was made to drop a virtual column generated by the system.
Action: None

ORA-26016: Virtual column string in table string.string cannot be loaded by direct path
Cause: You attempted to load an virtual column using the direct path.
Action: Virtual columns cannot be loaded using the direct path.

ORA-54000: Virtual column feature not yet implemented
Cause: Feature has not been implemented.
Action: Feature is being implemented.

ORA-54001: string: invalid identifier specified for virtual column expression
Cause: Column expression referenced a column that does not exist in the table.
Action: Rewrite column expression to reference only scalar columns in the table.

ORA-54002: only pure functions can be specified in a virtual column expression
Cause: Column expression contained a function whose evaluation is non-deterministic.
Action: Rewrite column expression to reference only pure functions.

ORA-54003: specified data type is not supported for a virtual column
Cause: Only scalar data types are supported for virtual columns. LONG, BLOB, REF, and BFILE data types are not supported for virtual columns.
Action: Specify the expression column with a supported scalar data type.

ORA-54004: resultant data type of virtual column is not supported
Cause: The data type of the underlying expression is not supported. Only scalar data types are supported for virtual columns. LONG, BLOB, REF, and BFILE data types are not supported for virtual columns.
Action: Specify the expression of virtual column to return a supported scalar data type.

ORA-54012: virtual column is referenced in a column expression
Cause: This virtual column was referenced in an expression of another virtual column
Action: Ensure the column expression definition for any virtual column does not refer to any virtual column

ORA-54013: INSERT operation disallowed on virtual columns
Cause: Attempted to insert values into a virtual column
Action: Re-issue the statment without providing values for a virtual column

ORA-54014: Resulting table from a CTAS operation contains virtual column(s)
Cause: Table being created by a CTAS operation contains a virtual column definition
Action: Remove the virtual column definition from the table being created

ORA-54017: UPDATE operation disallowed on virtual columns
Cause: Attempted to update values of a virtual column
Action: Re-issue the statment without setting values for the virtual column

ORA-54018: A virtual column exists for this expression
Cause: Specified index expression matches an existing virtual column”
Action: Re-issue the statment by replacing the index expression with the matching virtual column

ORA-54019: Virtual column expression cannot be changed because it is a partitioning column
Cause: Attempted to modify the expression of a virtual column that was also a partitioning column.
Action: This is not supported.

ORA-54020: Virtual column expression cannot be changed because it is a subpartitioning column
Cause: Attempted to modify the expression of a virtual column that was also a subpartitioning column.
Action: This is not supported.

ORA-54022: Virtual column expression cannot be changed because an index is defined on column
Cause: Attempted to change the expression of a virtual column that was indexed.
Action: Alter index unsable. Change expression and then rebuild index.

ORA-54023: Virtual column expression cannot be changed because a constraint is defined on column
Cause: Attempted to change the expression of a virtual column that had a constraint defined on it.
Action: Drop constraint and then change expression.

ORA-54025: Virtual column cannot have a default value
Cause: Attempted to alter a virtual column to have a default value.
Action: This is not valid, change the DDL.

ORA-54027: cannot modify data-type of virtual column
Cause: Attempted to change the data-type of virtual column without modifying the underlying expression
Action: change the underlying expression to be compatible with the data-type change

ORA-54028: cannot change the HIDDEN/VISIBLE property of a virtual column
Cause: Attempted to change the HIDDEN/VIRTUAL property of a virtual column
Action: re-issue the DDL without the virtual column property change

ORA-54029: Virtual column cannot be updated in trigger body
Cause: Attempted to change the value of virtual column in a trigger body”
Action: This is not valid, change the trigger definition.

ORA-54030: datatype mismatch between virtual column and expression
Cause: virtual column expression was changed after column was created”
Action: change the underlying expression to return datatype that conforms to the virtual column

ORA-54031: column to be dropped is used in a virtual column expression
Cause: Attempted to drop a column that was used in a virtual column expression.
Action: Drop the virtual column first or change the virtual column expression to eliminate dependency on the column to be dropped.

ORA-54032: column to be renamed is used in a virtual column expression
Cause: Attempted to rename a column that was used in a virtual column expression.
Action: Drop the virtual column first or change the virtual column expression to eliminate dependency on the column to be renamed.

ORA-54033: column to be modified is used in a virtual column expression
Cause: Attempted to modify the data type of a column that was used in a virtual column expression.
Action: Drop the virtual column first or change the virtual column expression to eliminate dependency on the column to be modified.

ORA-54034: virtual columns not allowed in functional index expressions
Cause: An attempt was made to create a functional index with an expression defined on one or more virtual columns.
Action: Specify the index expression using only regular columns.

ORA-54035: keyword HIDDEN cannot be specified here
Cause: Attempted to specify HIDDEN key word for a virtual column
Action: This is not supported.

ORA-54036: cannot define referential constraint with ON DELETE SET NULL clause on virtual column
Cause: Attempted to specify ON DELETE SET NULL clause for a referential integrity constraint on a virtual column.
Action: Reissue the statement without specifying ON DELETE SET NULL clause.