Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 7 down vote accepted

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
   numtodsinterval(sysdate-hiredate, 'day') delta
 from mytab;

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


01/01/12    247 9:15:55.0
share|improve this answer
+1 In the description Oracle Database Error Messages 11g Release 2 (…) of the following error it is explicitly stated that a deterministic function does not have to depend on the current time: ORA-30553: The function is not deterministic Cause: ...If it is not deterministic (it depends on package state, database state, current time, or anything other than the function inputs) ... – miracle173 Sep 4 '12 at 7:47

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.