English

Problem: Unable to create an Oracle materialized view containing an ST_Geometry attribute

Description

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".

Code:
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

Cause

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.

Code:
SQL> DESCRIBE sde.st_geometry
sde.st_geometry is NOT FINAL
Name Null? Type
------------------------------------------- -------- -----------
ENTITY NUMBER(38)
NUMPTS NUMBER(38)
MINX FLOAT(64)
MINY FLOAT(64)
MAXX FLOAT(64)
MAXY FLOAT(64)
MINZ FLOAT(64)
MAXZ FLOAT(64)
MINM FLOAT(64)
MAXM FLOAT(64)
AREA FLOAT(64)
LEN FLOAT(64)
SRID NUMBER(38)
POINTS BLOB

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".