Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

1 Answer 1

up vote 6 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
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
share|improve this answer
1  
+1 In the description Oracle Database Error Messages 11g Release 2 (docs.oracle.com/cd/E11882_01/server.112/e17766/…) 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
add comment

Your Answer

 
discard

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.