Using Inteval Datatype to store result of calculation

Question

I want to create a table which has hiredate and Interval of hiredate and sysdate. How to create a column that automaticlaly stores the calculation.

Answer 1

I don't believe you can.

The expression you use to create a virtual column must be deterministic, i.e. always return the same value when called with the same arguments. Involving the current date is fundamentally incompatible with that requirement.

You could use a plain old view though.

create table mytab (hiredate date);
create view myview
as
 select
   hiredate,
   numtodsinterval(sysdate-hiredate, 'day') delta
 from mytab;

insert into mytab values (to_date('20120101', 'YYYYMMDD'));
commit;
select * from myview;

Output:

HIREDATE        DELTA
01/01/12    247 9:15:55.0