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:
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
- The SQL would be:
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
- The SQL would be:
Question:
Is there a way to include a SHAPE column in a materialized view on a remote table with the fast refresh option?