Oracle Database 11g: Virtual database columns

Views in the Oracle database have several uses. You can use them to provide a view of data in different tables as a single object to query. You can use views to achieve a virtualization layer. Also views can be used to provide a user specific view of data. Implementing views however also have some challenges if you want to 'do it right'. You should consider grants to the table and the view. Maybe create synonyms. You should also consider what will happen if someone does access the underlying table since your data can now be queried from a different place (no single source of truth anymore). Do you want to have the view implement similar functionality as a table by providing an instead-of trigger when performing inserts on the view? Sometimes a view might seem too much for what you might want to accomplish. Suppose you want to add a single calculated field to a table. In this case there is a much easier solution than creating a view. A virtual column. The virtual column was introduced in Oracle Database 11g. In this blog post I'll give a simple minimal example of how you can use a virtual column and some things to mind when doing. Disclaimer: this code will not conform to many standards and is only meant as a minimal example.


Suppose we have the SCOTT schema with the EMP and DEPT tables:

CREATE TABLE "SCOTT"."EMP"
  (
    "EMPNO"    NUMBER(4,0),
    "ENAME"    VARCHAR2(10 BYTE),
    "JOB"      VARCHAR2(9 BYTE),
    "MGR"      NUMBER(4,0),
    "HIREDATE" DATE,
    "SAL"      NUMBER(7,2),
    "COMM"     NUMBER(7,2),
    "DEPTNO"   NUMBER(2,0),
    CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"),
    CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
  )

CREATE TABLE "SCOTT"."DEPT"
  (
    "DEPTNO" NUMBER(2,0),
    "DNAME"  VARCHAR2(14 BYTE),
    "LOC"    VARCHAR2(13 BYTE),
    CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
  )

Adding a virtual column

In the employee table we want to add a column for the location of the department. In order to keep it simple and thin, we do not want to create a view and avoid having to create additional objects, grants, synonyms, etc.

The expression used in the virtual column definition however has some restrictions:

• It cannot refer to another virtual column by name.
• It can only refer to columns defined in the same table.
• If it refers to a deterministic user-defined function, it cannot be used as a partitioning key column.
• The output of the expression must be a scalar value. It cannot return an Oracle supplied datatype, a user-defined type, or LOB or LONG RAW.

In order to reference a column from another table, we can use a function. PL/SQL functions however do not allow specifying the size of the return type. Thus while the LOC column in the SCOTT.DEPT table is 13 bytes, the virtual column will be of type varchar2 and thus use the maximum allowed size for a varchar2. In order to avoid this, you can cast the return value of the function to the correct size. You can also encounter the following error:

SQL Error: ORA-30553: The function is not deterministic
Cause: The function on which the index is defined is not deterministic
Action: If the function is deterministic, mark it DETERMINISTIC.  If it is not deterministic (it depends on package state, database state, current time, or anything other than the function inputs) then do not create the index.  The values returned by a deterministic function should not change even when the function is rewritten or recompiled.

Thus the function should be deterministic. See below for a working example of a function and a virtual column:

CREATE OR REPLACE FUNCTION SCOTT.GET_DEPT_LOC(
    p_deptno IN NUMBER)
  RETURN VARCHAR2 DETERMINISTIC
AS
  l_retval SCOTT.DEPT.loc%type;
BEGIN
  SELECT loc INTO l_retval FROM SCOTT.DEPT WHERE SCOTT.DEPT.DEPTNO=p_deptno;
  RETURN l_retval;
END;

ALTER TABLE "SCOTT"."EMP" ADD( LOC VARCHAR2(13 BYTE) GENERATED ALWAYS
AS
  (CAST(SCOTT.GET_DEPT_LOC(DEPTNO) AS VARCHAR2(13 BYTE) )));

The result looks as followed with LOC as the virtual column:

Serially reusable

Something to mind is when you are using packages which have PRAGMA SERIALLY_REUSABLE specified, and you have your function inside that package, you will encounter the following error:

ORA-06534: Cannot access Serially Reusable package string
Cause: The program attempted to access a Serially Reusable package in PL/SQL called from SQL context (trigger or otherwise). Such an access is currently unsupported.
 Action: Check the program logic and remove any references to Serially Reusable packages (procedure, function or variable references) which might happen in PL/SQL called from sql context (trigger or otherwise).

Using PRAGMA SERIALLY_REUSABLE can have performance benefits so I recommend to put the function in a separate package so the other package code can remain serially reusable.