Oracle Virtual Column

C_Users_PUB2_AppData_Local_Packages_Microsoft.SkypeApp_kzf8qxf38zg5c_LocalState_f06e5312-4e1b-408a-ae98-b6d2321598b5

admin DataBase, Oracle ,

Hello guys,
Hope you are doing well. Today, we will talk about the Oracle Virtual Column.
I will try to cover all important aspects in this blog.

What is a Virtual Column?

A virtual column is same as a normal column but when this column is queried, its values are derived based on the other columns rather than being stored on disc.

Here is the syntax to add a virtual column to a table.

column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only. We can omit these keywords as well.

The script below creates a table with a few columns including a virtual column. Email Domain is the virtual column and it is populated based on the Email column.



--Create Employees table with Virtual column Email_Domain should be updated based on Email column
CREATE TABLE employees (
    id           NUMBER,
    first_name   VARCHAR2(10),
    last_name    VARCHAR2(10),
    email        VARCHAR2(50),
    email_domain GENERATED ALWAYS as (substr(EMAIL,instr(EMAIL,'@')+1)) VIRTUAL) /* SUBSTR and INSTR two different functions */;

--Insert some data into the table    
INSERT INTO employees (id, first_name, last_name, email)
VALUES (1, 'JOHN', 'DOE', 'JOHN.DOE@ABC.COM');

INSERT INTO employees (id, first_name, last_name, email)
VALUES (2, 'JAYNE', 'DOE', 'JAYNE.DOE@ABC.COM');

--Commit
COMMIT;

Let’s query the table and see all the columns and the data in the Employees table.


SELECT * FROM employees;

        ID FIRST_NAME LAST_NAME  EMAIL                EMAIL_DOMAIN        
---------- ---------- ---------- -------------------- --------------------
         1 JOHN       DOE        JOHN.DOE@ABC.COM     ABC.COM             
         2 JAYNE      DOE        JAYNE.DOE@ABC.COM    ABC.COM                        
2 rows selected.

The expression used to generate the virtual column is listed in the DATA_DEFAULT column of the below listed views.

  1. DBA_TAB_COLUMNS
  2. ALL_TAB_COLUMNS
  3. USER_TAB_COLUMNS

SELECT
    column_name,
    data_default
FROM
    user_tab_columns
WHERE
    table_name = 'EMPLOYEES';

COLUMN_NAME     DATA_DEFAULT
--------------- ------------------------------------
ID
FIRST_NAME
LAST_NAME
EMAIL
EMAIL_DOMAIN    SUBSTR("EMAIL",INSTR("EMAIL",'@')+1)

5 rows selected.

Limitations of Virtual Column?

  • Virtual Column can only be used with Oracle 11g and later versions.
  • Virtual Columns cannot reference other virtual columns value. In this case below snippet code is invalid.

create table 
   t
(
   x number, 
   x1 as (x+1), 
   x2 as (x1+1)
);
  • Values cannot be inserted into the Virtaul Column. It is populated automatically based on the other columns as per the expressions.
  • It can only refer to columns defined in the same table.
  • Virtual columns can be referenced in the WHERE clause of updates and deletes, but they cannot be manipulated by DML.

If you have any questions please comment below or write me an email.
If you like this blog, do share with your friends and colleagues on your social media.
For more updates join my facebook group and do like my facebook page.

Thank you,
Kapil Kumar

Sharing is caring!

You May Also Like..

DUAL TABLE

Oracle_DUAL table

Hey geeks, DUAL table is a very important table available in the Oracle database. It gets created automatically during the […]

Concatenation Operator

Oracle_Concatenation Operator

Hey geeks, This is another chapter of Operators. Concatenation Operator helps to concatenate two or more strings or columns. The […]

Parentheses

Parentheses in SQL

Hey time travellers, Though Parentheses are used pretty much everywhere in SQL but in this blog, we will talk about […]

Leave a Reply to Anonymous Cancel reply

Your email address will not be published. Required fields are marked *