Materialized view with FAST refresh on remote table: How to include a SHAPE column?

Bud

 

I want to create an Oracle 18c materialized view with the fast refresh option on a remote table (in an enterprise GDB).

I can do this successfully without a SHAPE column:

create materialized view log on source_system.workorder with primary key;
grant select source_system.mlog$_workorder to schema_for_dblink;

create materialized view my_gis_schema.wo_mv
build immediate 
refresh fast
start with sysdate next sysdate + (15/(60*60*24))
as
select
    cast(workorderid as number(38,0)) as objectid, --workorderid is a fake primary key; it has a NOT NULL constraint and a unique index
    wonum,
    status,
    --other fields
    longitudex,
    latitudey
from
    source_system.workorder@my_dblink

---

Problem:

I want to store the XY coordinates from the source table in a SHAPE column in the MV. Unfortunately, my options seem pretty limited:

1. Oracle doesn’t seem to support SDE.ST_GEOMETRY in MVs (more info here and here).

   • The SQL would be: 
     sde.st_geometry(longitudex,latitudey,null,null, 26917 ) as shape
     
     

2. Additionally, Oracle doesn't seem to support SDO_GEOMETRY in MVs on a remote table with the fast refresh option: 
   ORA-12015: cannot create a fast refresh materialized view from a complex query

   • The SQL would be: 
     sdo_geometry(2001, 26917, sdo_point_type(longitudex,latitudey, null), null, null) as shape

---

Question:

Is there a way to include a SHAPE column in a materialized view on a remote table with the fast refresh option?