Suppose we have the SCOTT schema with the EMP and DEPT tables:
CREATE TABLE "SCOTT"."EMP"
"ENAME" VARCHAR2(10 BYTE),
"JOB" VARCHAR2(9 BYTE),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"),
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
CREATE TABLE "SCOTT"."DEPT"
"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
SELECT loc INTO l_retval FROM SCOTT.DEPT WHERE SCOTT.DEPT.DEPTNO=p_deptno;
(CAST(SCOTT.GET_DEPT_LOC(DEPTNO) AS VARCHAR2(13 BYTE) )));
The result looks as followed with LOC as the virtual column:
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.