Oracle Virtual Columns

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Virtual Columns

Since 11g is possible to create columns based on functions, not physically stored on database. See the example below:

create table sales
    (
       sales_id      number,
       cust_id       number,
       sales_amt     number,
       sale_category varchar2(6)
       generated always as
       (
          case
            when sales_amt <= 10000 then 'LOW' 
            when sales_amt > 10000 and sales_amt <= 100000 then 'MEDIUM' 
            when sales_amt > 100000 and sales_amt <= 1000000 then 'HIGH'
            else 'ULTRA'
          end
       ) virtual
   );

It’s also interesting to raise that starting on Release 2, virtual columns can be used as foreign keys. Should be used as FK, not sure… but can….

Cheers!