4

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.

7

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
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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