Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Virtual Columns in Oracle Database 11g Release 1

When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below.

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.

The script below creates and populates an employees table with two levels of commission. It includes two virtual columns to display the commission-based salary. The first uses the most abbreviated syntax while the second uses the most verbose form.

CREATE TABLE employees (
  id          NUMBER,
  first_name  VARCHAR2(10),
  last_name   VARCHAR2(10),
  salary      NUMBER(9,2),
  comm1       NUMBER(3),
  comm2       NUMBER(3),
  salary1     AS (ROUND(salary*(1+comm1/100),2)),
  salary2     NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,
  CONSTRAINT employees_pk PRIMARY KEY (id)
);

INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (1, 'JOHN', 'DOE', 100, 5, 10);

INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (2, 'JAYNE', 'DOE', 200, 10, 20);
COMMIT;

Querying the table shows the inserted data plus the derived commission-based salaries.

SELECT * FROM employees;

        ID FIRST_NAME LAST_NAME      SALARY      COMM1      COMM2    SALARY1    SALARY2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1 JOHN       DOE               100          5         10        105        110
         2 JAYNE      DOE               200         10         20        220        240

2 rows selected.

SQL>

The expression used to generate the virtual column is listed in the DATA_DEFAULT column of the [DBA|ALL|USER]_TAB_COLUMNS views.

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

COLUMN_NAME                    DATA_DEFAULT
------------------------------ --------------------------------------------------
ID
FIRST_NAME
LAST_NAME
SALARY
COMM1
COMM2
SALARY1                        ROUND("SALARY"*(1+"COMM1"/100),2)
SALARY2                        ROUND("SALARY"*(1+"COMM2"/100),2)

8 rows selected.

SQL>

Notes and restrictions on virtual columns include:

Here is an example of Virtual Column-Based Partitioning.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.