Problem: Unable to create an Oracle materialized view containing an ST_Geometry attribute
Creating an Oracle materialized view for a table containing an ST_Geometry attribute returns the following error:
"ORA-30373: object data types are not supported in this context".
SQL> CREATE MATERIALIZED VIEW parcel_view
2 AS SELECT * FROM parcel@remote_server;
CREATE MATERIALIZED VIEW parcel_view
ERROR at line 1:
ORA-30373: object data types are not supported in this context
Oracle's Advanced Replication functionality requires that all object types be defined as FINAL to participate within a materialized view.
Describe the ST_Geometry type in SQL*Plus and notice that the type is not defined as FINAL.
SQL> DESCRIBE sde.st_geometry
sde.st_geometry is NOT FINAL
Name Null? Type
------------------------------------------- -------- -----------
The reason that the ST_Geometry cannot be defined as FINAL is because the type contains subtypes used for type inheritance. Oracle does not allow a type with subtypes to be defined as FINAL.
This is not a limitation with Esri's implementation of the ST_Geometry type.
Solution or Workaround
The only solution is to not reference the ST_Geometry attribute in the materialized view until the problem has been resolved by Oracle.
Esri is currently working with Oracle to address this issue and limitation. The following Oracle TAR file is available for reference: "6482996.992 - ORA-30373 MATERIALIZE VIEW UNABLE TO REPLICATE A TYPE WHICH CONTAINS SUBTYPES. Enhancement Request (ER) 6370112".