Sysdate in oracle virtual column

Question

I'm trying to create a virtual column in oracle that use a case statement but if I call the SYSDATE function it gave me this error:

ORA-54002: only pure functions can be specified in a virtual column expression

This is the query:

alter table t_requirements ADD
  REQUIREMENT_STATE varchar2(30)
  generated always as(
    CASE
     WHEN t_requirements.activation_date- SYSDATE - 5 <= 0 AND 
       t_requirements.activation_date - SYSDATE > 0 THEN 'Exist'
     WHEN t_requisiti.activation_date - SYSDATE <=0 THEN 'Active'
    END) virtual;

Answer 1

You are using SYSDATE to build your virtual column. This is not allowed, because SYSDATE is not deterministic, i.e. it doesn't always return the same value. Imagine you build an index on this column. A second later the index would already be invalid!

It seems you should rather be writing a view containing this ad hoc computed column.

Answer 2

sysdate is not deterministic. Its value can vary each time we run it. Virtual columns must be deterministic. Otherwise there would be the bizarre situation in which querying a record would changes its value. Quite rightly Oracle doesn't allow that.

This is a scenario where we still have to use a query (perhaps as a view over the table) to display a derived value.

select r.*
     , cast(
       CASE
          WHEN r.activation_date- SYSDATE - 5 <= 0 AND 
               r.activation_date - SYSDATE > 0 THEN 'Exist'
          WHEN r.activation_date - SYSDATE <=0 THEN 'Active'
          else 'Inactive'
       END 
       as  varchar2(30)) as REQUIREMENT_STATE
from requirements r
;

Incidentally, does your CASE statement need an ELSE? To display something other than a blank when activation_date is greater than SYSDATE+5