By using the DETERMINISTIC clause, I am able to create a virtual columns that
"works," but I feel like we're treading into dangerous territory.Indeed you are. Data corruption territory.
I assume you're getting purity errors
SQL> create table GST_DOMAIN_ROLE
2 (
3 ROLE_ID number(19),
4 DESCRIPTION varchar2(255 char),
5 ACTIVE_FROM date,
6 ACTIVE_TO date,
7 PARTY_ID number(19),
8 IS_ACTIVE number(1) generated always as (case when sysdate between coalesce(ACTIVE_FROM, sysdate) and coalesce(ACTIVE_TO, sysdate)
9 then 1
10 else 0
11 end)
12 );
IS_ACTIVE number(1) generated always as (case when sysdate between coalesce(ACTIVE_FROM, sysdate) and coalesce(ACTIVE_TO, sysdate)
*
ERROR at line 8:
ORA-54002: only pure functions can be specified in a virtual column expression
You could use a view to workaround that.
SQL>
SQL> create table "_GST_DOMAIN_ROLE"
2 (
3 ROLE_ID number(19),
4 DESCRIPTION varchar2(255 char),
5 ACTIVE_FROM date,
6 ACTIVE_TO date,
7 PARTY_ID number(19)
8 );
Table created.
SQL>
SQL> create or replace
2 view GST_DOMAIN_ROLE as
3 select g.* ,
4 case when sysdate between coalesce(ACTIVE_FROM, sysdate) and coalesce(ACTIVE_TO, sysdate)
5 then 1
6 else 0
7 end is_active
8 from "_GST_DOMAIN_ROLE" g ;
View created.