Oracle’s Virtual Columns

Since the version 11g Oracle supports so called virtual columns. Virtual columns consist of a calculation depending on other columns in a table. They are not stored persistently in the tablespace but calculated during run time. These virtual columns can be useful sometimes. For example if you want to implement interval partitioning on numbers in a varchar column (Oracle only supports interval partitioning on date or number columns). Or if you want to enhance an existing table easily or for testing reasons by new calculation without editing the complete table loading routine.

In order to build a virtual column in a table you easily add it to the CREATE TABLE statement as in the following example:

CREATE TABLE Test(
                ID               NUMBER,
                QTY              NUMBER(5,0),
                PRICE            NUMBER(10,2),
                TURNOVER        NUMBER GENERATED ALWAYS AS (QTY*PRICE) VIRTUAL
);

As you can easily see in this example the turnover column is a virtual column. During run time Oracle calculates the turnover by multiplying the quantity by the price.

If you want to insert new values you have to keep in mind that you cannot insert values in virtual columns. For that you have to explicitly define the relevant columns when inserting datasets:

 INSERT INTO Test (ID, QTY, PRICE) VALUES (1, 10, 5.75);

There are some other restrictions:

  • Virtual columns can’t be based on other virtual columns, i.e. only on “normal” columns
  • If the virtual column’s expression contains a user defined functions, it can’t be used as a partitioning key.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!

Share and Enjoy: Diese Icons verlinken auf Bookmark Dienste bei denen Nutzer neue Inhalte finden und mit anderen teilen können.
  • LinkedIn
  • XING
  • Facebook
  • Google Bookmarks
  • Twitter

Hinterlasse eine Antwort

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind markiert *