CREATE MATERIALIZED VIEW ON ArcSDE

2117
5
10-30-2012 08:59 PM
New Contributor
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_Geometr...).

I want to know what's wrong and how can I do?
Reply
0 Kudos
5 Replies
Esri Esteemed Contributor
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
Reply
0 Kudos
New Contributor
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
Reply
0 Kudos
Esri Esteemed Contributor
It would be more accurate to say that Oracle doesn't support materialized views
on SDE.ST_GEOMETRY.

- V
Reply
0 Kudos
New Contributor
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
Reply
0 Kudos
Esri Esteemed Contributor
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
Reply
0 Kudos