topic Re: Identify doesn't work when using views in Oracle (bug?) in Data Management Questions

Identify doesn't work when using views in Oracle (bug?)

MortenHansen — Fri, 28 Dec 2012 09:01:34 GMT

The Identify/info function in ArcMap (or via javascript API in a web application) doesn't work when using a View in Oracle.

Identify works fine on a simple feature class (eg. "MyFeatureClass" with an ID, a shape and a text attribute column), but when I create a simple view, eg. "SELECT * FROM MyFeatureClass", the identify function doesn't give any result, no matter where I click.

Both the feature class and the view are created via ArcCatalog in ArcMap.

The problem was identified using ArcGIS Server and the Javascript ArcGIS API, but the same problem exists using ArcMap directly on the database connection.

If I use the exact same setup, but with SQL Server instead of Oracle, the problem dissapears.

I am using ArcGIS 10.1.

Is it a bug in ArcGIS 10.1 (in connection with Oracle)?

Re: Identify doesn't work when using views in Oracle (bug?)

VinceAngelo — Fri, 28 Dec 2012 11:15:59 GMT

What is the geometry storage type of the two tables in the two databases?

'sdelayer -o describe' output on the four objects (two tables and two views)
should tell the tale of what has gone wrong.

- V

Re: Identify doesn't work when using views in Oracle (bug?)

MortenHansen — Sat, 11 Dec 2021 18:34:07 GMT

Hi vangelo

Thank you very much for your reply.

The 'sdelayer -o describe' outputs for the two feature classes are:

ArcSDE 10.1  for SQL Server Build 679 Thu Apr  5 11:43:21  2012
Layer   Administration Utility
-----------------------------------------------------
----------------------------------------------------------------
Database           : MYDB
Table Owner        : DBO
Table Name         : TESTSIMPLE
Spatial Column     : SHAPE
Layer id           : 2
Entities    : nac+
Layer Type         : In-Line Spatial Type 
I/O Mode    : NORMAL
Autolocking        : Enabled
Precision          : High
User Privileges    : SELECT, UPDATE, INSERT, DELETE
Layer Configuration: DEFAULTS

and

ArcSDE 10.1  for Oracle11g Build 679 Thu Apr  5 11:43:21  2012
Layer   Administration Utility
-----------------------------------------------------
----------------------------------------------------------------
Table Owner        : SDE
Table Name         : TESTSIMPLE
Spatial Column     : SHAPE
Layer id           : 13
Entities    : nac+
Layer Type         : Extended SQL Type 
I/O Mode    : NORMAL
Autolocking        : Enabled
Precision          : High
User Privileges    : SELECT, UPDATE, INSERT, DELETE
Layer Configuration: DEFAULTS

The 'sdelayer -o describe' tool does not give me any information about views.

Re: Identify doesn't work when using views in Oracle (bug?)

VinceAngelo — Mon, 14 Jan 2013 12:11:59 GMT

The 'sdelayer -o describe' tool does not give me any information about views.

And therein lies your problem. Views must be registered with the geodatabase (either via
'sdelayer -o register' or the new 10.1 methodology). The same holds true for SQL-Server,
unless you used 'sdetable -o create_view' to create the spatial view.

- V

Re: Identify doesn't work when using views in Oracle (bug?)

MortenHansen — Sat, 11 Dec 2021 18:34:11 GMT

The views are created within ArcCatalog.

The "Register with Geodatabase" option in the right-click menu is disabled on the views.

If I try to run the "Register with Geodatabase" geoprocessing tool in ArcToolbox, it returns the following error:

Messages
...
ERROR 001399: Views are not supported.
Failed to execute (RegisterWithGeodatabase).
Failed at Mon Jan 14 16:02:34 2013 (Elapsed Time: 0,00 seconds)

Re: Identify doesn't work when using views in Oracle (bug?)

VinceAngelo — Mon, 14 Jan 2013 13:39:44 GMT

Looks like I gave you a bad link, since "Register with Geodatabase" is not correct
for views. Try these links for SQL-Server and Oracle. I'm a SQL guy, so I've never
used Desktop to create a view, but something went wrong in your methodology,
since ArcSDE should be able to describe the views if they were geodatabase-ready
(maybe you created the view using a Query Layer connection?)

- V

Re: Identify doesn't work when using views in Oracle (bug?)

MortenHansen — Mon, 14 Jan 2013 14:16:19 GMT

I am using the same approach for SQL Server and Oracle.

The problem only occurs in Oracle. Also,

I can add the data from both data sources to ArcMap and see the maps. The problem is the "identify" tool, that doesn't give any result when using Oracle.

However, I will use a different approach (a workaround) using a feature class rather than a view to solve the current issue.

Thank you very much for taking the time to help me, Vangelo.

Re: Identify doesn't work when using views in Oracle (bug?)

ForrestJones — Tue, 15 Jan 2013 15:50:59 GMT

Hi Morten,

A couple questions for you:

Are you using 10.1 final? Also, do you have any error messages in the sdedc_Oracle.log in your user %TEMP% directory?

Thanks.

Re: Identify doesn't work when using views in Oracle (bug?)

AnnalieseVollick — Fri, 22 Mar 2013 14:56:47 GMT

I found that Views drew fine in ArcMap but in order to Identify on Oracle (11g, on ArcSDE 10.1) ST_GEOMETRY views, I had to issue command line sdelayer -o register on each View as noted above. Once that was done for each View, I could identify/select in ArcMap, also, Area and Length were brought through as well.

Once you issue sdelayer -o register on the view, actually, right click > Manage > Register with Geodatabase becomes available (grey before) in ArcCatalog. (I discovered that when looking for something else!).

Annaliese

Re: Identify doesn't work when using views in Oracle (bug?)

ANDREACARVELLI — Thu, 23 May 2013 07:22:34 GMT

Hi Guys, I've exactly the same issue using Oracle views...ArcMap Identify task doesn't work at all 😞 Did you find a workaroud on that? I'm looking also a solution for the server tool "ExtractDataTask" that with views and query layers doesn't works at all...
Any suggestions?

Re: Identify doesn't work when using views in Oracle (bug?)

JeffPace — Tue, 02 Jun 2015 12:55:24 GMT

Did anyone ever find a solution to this? WE just ran into this (SDE 10.2.2 on Oracle11g)

Re: Identify doesn't work when using views in Oracle (bug?)

PanagiotisPapadopoulos — Mon, 12 Mar 2018 08:22:32 GMT

If the the layer you are using is a query layer you need to enable the St_Geometry library on Oracle Server.

Configure the extproc to access ST_Geometry in Oracle—Help | ArcGIS Desktop