PL/SQL: ORA-21700 object does not exist. . . Oracle10g to SDE 10/Oracle11G

[RobertParsons]

Fellow ArcSDE Professionals:
We currently have an Oracle10G asset management system that select new records on our ArcSDE database through a multi-versioned view that then creates new assests in the Asset Management System. 

We have just upgraded to ArcSDE 10 on Oracle 11G and when we attempt to compile the PL/SQL on the Oracle10G instance the following errors occur:

Error(218,4): PL/SQL: SQL Statement ignored
Error(219,20): PL/SQL: ORA-21700: object does not exist or is marked for delete
Error(230,4): PL/SQL: SQL Statement ignored
Error(231,20): PL/SQL: ORA-21700: object does not exist or is marked for delete
Error(242,4): PL/SQL: SQL Statement ignored
Error(243,20): PL/SQL: ORA-21700: object does not exist or is marked for delete

The old database we were using SDEBinary, on the new database we are using ST_GEOMETRY. 

Thanks in advance for any insight and or troubleshooting tips.

Rob Parsons
City of Columbus, Ohio

[anthonysanchez]

Hi rob,
If you migrated your storage from sdelob to stgeometry you'll need to recreate your materialized view because the structure is different. There are no longer F or S tables in stgeometry, and these tables are likely still being referenced in your materialized view and giving you the errors.

Anthony

[RobertParsons]

Anthony,
Thanks for reply. I have seen the Oracle Metalink article that you are referring too.  So, does that mean that an ArcSDE Multi-Versioned View is another term for Oracle Materialized View?  If so, then when we upgraded from 9.3.1 to 10 I moved the data into a brand new system (Oracle11G)  Versioned the features then created the Multi-Versioned Views.  The PLSQL that is selecting data from the Multi-Versioned view is still in Oracle 10G.  From that 10G database I can login using SQLPlus and perform the queries with no problem against the multi-versioned view but the plsql package cannot.

I will look into how Oracle "sees" the multi-versioned view, maybe that will give me some clues, but any other additional troubleshooting tips would be greatly appreciated.

Rob P.

[anthonysanchez]

Oops. I meant multi versioned view which is just an oracle view.

Anthony

[RobertParsons]

Anthony,
The multi-versioned views are brand new, created after I moved the data from ArcSDE 9.3.1 to ArcSDE 10.  Do I need to just select the mv views from the asset manangement system through sql so that the asset management resets to st_geometry.  I would have thought that a recompile would accomplish that.

Thanks,
Rob

[RobertParsons · Accepted solution]

For those that are interested. It has been resolved.  In fact the error that is in the Title of this thread was not the real cause it was a ORA-22804. Remote operations not permitted on object tables with a user-defined type.  We moved to the ST_GEOMETRY datatype from SDEBinary. As this was a dblink to the GIS ArcSDE Database from the Asset management system it did not work.  We altered the PLSQL and it is now working.  In the future if you are selecting from a multi-versioned view do not include the shape field if you are using ST_GEOMETRY.

Thanks to ESRI Tech Support for nailing this one down.

Happy Oracleing,
Rob

[RobynGallagher]

Hi Rob

We have seen the same issue and your post confirms what our DBA thought was the cause.

If you're still monitoring this, can I clarify: I understand you are saying you can make this materialized view across data base links work for ST_GEOMETRY if you don't actually reference the geometry field? That's not what we are seeing - as long as there is an ST_GEOMETRY field, the materialized view (which doesn't try to pick up the geometry) fails. Are we doing something else wrong, or am I misunderstanding your last post?

thanks
Robyn Gallagher
Geoscience Australia

[RobertParsons]

Robyn,

My apologies, I had not been monitoring this and have since changed jobs. However, I can answer that question, in that on the remote database the sql that you write to select from the Geo-aware database should not include the shape field. The ArcSDE multi-versioned view is just a view not a materialized view. 

     For example:  select attr1, attr2 from geoAwareDatabase@remote_oracle would be good.

     Not Good:  select attr1, attr2, shape from geoAwareDatabae@remote_oracle;

Going through the DBLink would not work.  Now with updates and upgrades that may have changed.

Happy Oracleing,

Rob P.