1

Occasionally, schema changes that are made in my tables cause errors in my views. Example: a field is changed in a table that was referenced by a view.

When there are errors in a view, ArcGIS Desktop throws errors:

  • When the view is dragged to ArcMap, error: 'Could not add the specified data object to the map. DBMS table not found [ORA-24372: invalid object for describe]'
  • When double clicked in Catalog, error: 'Failed to edit the selected object(s).'
  • When right clicked >> Properties in Catalog: Nothing happens

I would like to either modify/fix the view's definition(SQL) or, at the very least, get the SQL, and then delete the broken VIEW and recreate it with working SQL. However, because there are errors, ArcGIS is not letting me get to the view's properties or the view's definition tab, to get at the SQL.

How can I fix the definition(SQL) of a view that has errors in ArcGIS Desktop?

Environment:

  • ArcGIS Desktop 10.3.1
  • SDE 10.3.1
  • Oracle 12c (12.1.0.2.0)

I do not have access to SQL Plus. I only have ArcGIS Desktop and MS Access, and do not have DBA privileges.

  • Best practice is to define views in scripts which can be re-run at need. If tables are changed, the script would then need to be altered and re-run. Not having a native SQL client for database administration is a major flaw (especially considering the SQL-Plus addition is such a trivial addition to an Instant Client deployment (which isn't a registry item)), but arcpy.ArcSDESQLExecute can run SQL scripts if you parse them correctly. Please edit your question to specify the exact release of Oracle, since "11g" is two families of releases. – Vince Aug 9 '16 at 15:42
  • If you cannot update the view definitions in Oracle perhaps you can use query layers instead of views? You can use the view definition logic in the Create Query Layer dialog to create and store query layers which should behave much as views do. – Dowlers Aug 9 '16 at 21:34
  • You may not have access to sqlplus but you surely have someone (à DBA) looking after it. Ask him/her to fix the view. – Albert Godfrind Aug 10 '16 at 9:11
  • @Vince 1) Your suggestion of using scripts to create views is a good one. Will do. 2) Yes, you're right. I need SQL-Plus. I am also looking into arcpy.ArcSDESQLExecute. 3) The Oracle version in the qeustion has been updated. Your comments were extremely helpful - thanks. – Wilson Aug 10 '16 at 17:07
  • @Dowlers You're right, Query Layers would work in a pinch, but I'm really trying to stick with database views right now. – Wilson Aug 10 '16 at 17:09
0

I was able to query the view definition (using an MS Access Pass-through Query) to get the SQL as text:

select TEXT from ALL_VIEWS where view_name = 'my_view'

Once I had retrieved the broken SQL, I deleted the view, and then recreated it using corrected SQL.

To avoid this situation in the future, I'll take Vince's suggestion:

Best practice is to define views in scripts which can be re-run at need. If tables are changed, the script would then need to be altered and re-run.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.