I want to create an Oracle 18c materialized view (MV) on a table that has a user-defined datatype called ST_GEOMETRY:
- The MV would do a COMPLETE refresh on a schedule — on a local table.
Some info about ST_GEOMETRY:
The ST_Geometry data type implements the SQL 3 specification of user-defined data types (UDTs), allowing you to create columns capable of storing spatial data such as the location of a landmark, a street, or a parcel of land. It provides International Organization for Standards (ISO) and Open Geospatial Consortium, Inc. (OGC) compliant structured query language (SQL) access to the geodatabase and database. This storage extends the capabilities of the database by providing storage for objects (points, lines, and polygons) that represent geographic features. It was designed to make efficient use of database resources; to be compatible with database features such as replication and partitioning; and to provide rapid access to spatial data.
ST_Geometry itself is an abstract, noninstantiated superclass. However, its subclasses can be instantiated. An instantiated data type is one that can be defined as a table column and have values of its type inserted into it.
The SQL definition of the user-defined datatype can be found here: db<>fiddle.
CREATE OR REPLACE TYPE SDE."ST_GEOMETRY" ( ... ) NOT final
(Although, I'm guessing most of the logic is stored in the EXTPROC.)
MV object types must be FINAL:
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
Oracle's Advanced Replication functionality requires that all object types be defined as FINAL to participate within a materialized view.
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.
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".
Note: I've contacted ESRI support and they say there hasn't been any progress with Oracle regarding the enhancement request.
Is there a way to workaround this issue?
- I ask because ESRI has a reputation of not being great with Oracle. So there is a chance that they have misunderstood the issue or haven't gotten creative enough.
For example, what about the ONLY keyword?
For both object-relational and object materialized views that are based on an object table, if the type of the master object table is not FINAL, the FROM clause in the materialized view definition query must include the ONLY keyword.
CREATE MATERIALIZED VIEW customer OF cust_objtyp AS SELECT CustNo FROM ONLY HR.Customer_objtab@dbs1;
Otherwise, the FROM clause must omit the ONLY keyword.
Unfortunately, that blurb is about object tables, not about object columns (I believe the ST_GEOMETRY user-defined datatype would be considered an "object column").
But I wonder if the concept of the ONLY keyword could be used to help with the FINAL problem with the ST_GEOMETRY object column. Any ideas?