Database.fi

When I found out about virtual columns I just had to try it.
Just a simple example for our familiar employees table.

First I made a copy of the employees:
Create table duunarit as select * from employees;


SQL> desc duunarit
Name Null? Type
----------------------------------------- -------- ---------------------

EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)


I would like to add a salgrade column to the table with some simple reasoning based on salary:
when salary is less than 3000, print ‘Poor you’
when salary is 3000 – 5000, print ‘Lucky you’
when it is above 5000, print ‘You bastard’

So here is my column definition:

alter table duunarit add
(salgrade varchar2(20)
generated always as
(
case
when salary < 3000 then 'Poor you'
when salary < 5000 then 'Lucky you'
else 'You bastard'
end
)virtual
);

Table altered.


Let's try it!

SQL> l
1* select last_name, salary, salgrade from duunarit

LAST_NAME SALARY SALGRADE
------------------------- ---------- --------------------
Sciarra 7700 You bastard
Urman 7800 You bastard
Popp 6900 You bastard
Raphaely 11000 You bastard
Khoo 3100 Lucky you
Baida 2900 Poor you
Tobias 2800 Poor you
Himuro 2600 Poor you
Colmenares 2500 Poor you
Weiss 8000 You bastard
Fripp 8200 You bastard


This looks great functionality!

Just a quick reminder about maybe the most common small problem in new 11g installations / upgrades.
When entering the 11g world, remember to do something to the default setting of password life time of 180 days. If you do nothing, your users / schemas will cease to work after half an year from creation.
select profile, limit from dba_profiles where resource_name = ‘PASSWORD_LIFE_TIME’;
PROFILE LIMIT
—————————— —————————————-
DEFAULT 180
MONITORING_PROFILE DEFAULT
If you want to remove the limits altogether, here is how to do it:
SQL> alter profile default limit PASSWORD_LIFE_TIME unlimited;
Profile altered.
Verify with:
1* select profile, limit from dba_profiles where resource_name = ‘PASSWORD_LIFE_TIME’
PROFILE LIMIT
—————————— —————————————-
DEFAULT UNLIMITED
MONITORING_PROFILE DEFAULT