topic Re: CREATE MATERIALIZED VIEW ON SDE.ST_GEOMETRY in Data Management Questions

CREATE MATERIALIZED VIEW ON ArcSDE

zmtylsabc — Wed, 31 Oct 2012 03:59:53 GMT

I use ArcSDE V10.0 for Oracle10g(Oracle 10.2.0.4).Now I create a materialized view as following:
CREATE MATERIALIZED VIEW g_switch
AS SELECT objectid,shape FROM g_gadgets where class='SWITCH';

But it return s that:
"ORA-30373: object data types are not supported in this context".

The type of field "OBJECTID" is INTEGER and "Shape" is ST_GEOMETRY.

but in Arcgis Desktop V10.0 help document,it works well(http://help.arcgis.com/en/arcgisdesktop/10.0/help/#/Using_spatial_views_on_tables_with_an_ST_Geometry_column/006z00000039000000/).

I want to know what's wrong and how can I do?

Re: CREATE MATERIALIZED VIEW ON SDE.ST_GEOMETRY

VinceAngelo — Wed, 31 Oct 2012 09:43:34 GMT

It's often useful to do a search on the error message. When I searched on "ORA-30373", the third
reference was a Knowledge Base document, Since "materialized view" is just a fancy name for
a table that is periodically repopulated by a query, you can model this yourself with SQL via
CREATE TABLE ... AS SELECT ..., TRUNCATE TABLE, and INSERT INTO ... SELECT (be sure to
manage the indexes as efficiently as possible)

- V

Re: CREATE MATERIALIZED VIEW ON SDE.ST_GEOMETRY

zmtylsabc — Wed, 31 Oct 2012 13:59:03 GMT

sorry.I don't understand what you've said.It seems that ST_GEOMETRY type does not be supported by oracle MATERIALIZED VIEW .

It's often useful to do a search on the error message. When I searched on "ORA-30373", the third
reference was a Knowledge Base document, Since "materialized view" is just a fancy name for
a table that is periodically repopulated by a query, you can model this yourself with SQL via
CREATE TABLE ... AS SELECT ..., TRUNCATE TABLE, and INSERT INTO ... SELECT (be sure to
manage the indexes as efficiently as possible)

- V

Re: CREATE MATERIALIZED VIEW ON ArcSDE

VinceAngelo — Wed, 31 Oct 2012 14:48:04 GMT

It would be more accurate to say that Oracle doesn't support materialized views
on SDE.ST_GEOMETRY.

- V

Re: CREATE MATERIALIZED VIEW ON ArcSDE

zmtylsabc — Thu, 01 Nov 2012 00:13:48 GMT

But in Arcgis Desktop V10.0 help document,it works well(http://help.arcgis.com/en/arcgisdesk...0000039000000).why?

It would be more accurate to say that Oracle doesn't support materialized views
on SDE.ST_GEOMETRY.

- V

Re: CREATE MATERIALIZED VIEW ON ArcSDE

VinceAngelo — Thu, 01 Nov 2012 00:35:39 GMT

It should work, but it doesn't, for the reasons described in the KB doc.

I've requested that the Oracle example be removed from the Help until
it works properly.

- V