Identify Fails w/ Oracle Database Views

[JosephBuckles]

I've created database views using ArcGIS desktop. (10.4) Oracle (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)

The views work just fine as is when they were created in 10.2.2. We've upgraded to 10.4 and want to re-create these views since we no longer have access to command line.  Upon the creation of these views they draw just fine but we cannot Identify them. We also cannot export. But we can still perform selections.

Is there something we are missing?  These views fail also when creating them a Query Layer in ArcMap.

Thanks in advance.

[AsrujitSengupta · Accepted solution]

You can try creating the View using 10.2.2 SDE Command line tools and check if that works.

Also, is Extproc configured for that Oracle geodatabase?

[JosephBuckles]

Thank you Asrujit, I am looking into the Extproc now.

[JosephBuckles]

It was the Extproc that was the issue.

Thank you for your help. We worked with the DBA to get the correct files over there.

Joe

[BirajaNayak]

Hi Joseph,

How you are creating view in 10.4. Please provide detail workflow including script used,

Have you tried creating view using geoprocessing tool called "create database view".

Regarding query layer, how you are creating query layer and what is the error message.

Thanks,

Biraja

[Anonymous User]

Hi

Did you check if OBJECTID field is included in the view and if the values in this field are unique?

Regards

Jesús de Diego

[JosephBuckles]

I Just tried the the Script you mentioned.  It's behavior was no different.

Here is an example of the View:

SELECT

b.OBJECTID,

b.BUILDINGID,

b.FACILITYKEY,

b.SHORTNAME,

b.LONGNAME,

b.BLDGAREA,

b.FLOORCOUNT,

b.BLDGTYPE,

b.OPERHOURS,

b.OPERDATE,

b.ACCESSTYPE,

b.CONDITION,

b.OWNEDBY,

b.MAINTBY,

b.LASTUPDATE,

b.LASTEDITOR,

b.BUILDING_HEIGHT,

b.CREATED_USER,

b.CREATED_DATE,

b.LAST_EDITED_USER,

b.LAST_EDITED_DATE,

b.SPEC_ID,

b.SHAPE,

0 SDE_STATE_ID

FROM GISSA.BUILDING b,

(SELECT SDE_DELETES_ROW_ID,SDE_STATE_ID FROM GISSA.D238 WHERE SDE_STATE_ID = 0 AND SDE.version_util.in_current_lineage (DELETED_AT) > 0) d

WHERE b.OBJECTID = d.SDE_DELETES_ROW_ID(+) AND d.SDE_STATE_ID IS NULL  AND SDE.version_util.get_lineage_list > 0 UNION ALL

SELECT

a.OBJECTID,

a.BUILDINGID,

a.FACILITYKEY,

a.SHORTNAME,

a.LONGNAME,

a.BLDGAREA,

a.FLOORCOUNT,

a.BLDGTYPE,

a.OPERHOURS,

a.OPERDATE,

a.ACCESSTYPE,

a.CONDITION,

a.OWNEDBY,

a.MAINTBY,

a.LASTUPDATE,

a.LASTEDITOR,

a.BUILDING_HEIGHT,

a.CREATED_USER,

a.CREATED_DATE,

a.LAST_EDITED_USER,

a.LAST_EDITED_DATE,

a.SPEC_ID,

a.SHAPE,

a.SDE_STATE_ID

FROM GISSA.A238 a,

(SELECT SDE_DELETES_ROW_ID,SDE_STATE_ID FROM GISSA.D238 WHERE SDE.version_util.in_current_lineage (DELETED_AT) > 0) d

WHERE

a.OBJECTID = d.SDE_DELETES_ROW_ID(+) AND a.SDE_STATE_ID = d.SDE_STATE_ID(+) AND SDE.version_util.in_current_lineage (a.SDE_STATE_ID) > 0 AND d.SDE_STATE_ID IS NULL !

[JosephBuckles]

I am trying to create the view by by going to the connection in ArcCatalog right clicking and choosing new View. 

I am using the same SQL from the 10.2.2 created view and just copying it into the dialog.  (This database was updated to 10.4 and the 10.2.2 created views are still there and still performing).

I have also tried it with the same process of creating a query layer in ArcMap.

Copy and past the SQL in the dialog .

I thought that maybe something was wrong with the SQL Statement so I decided to make a simple view doing nothing more than a SELECT * FROM statement on a table.

This too fails when I attempt to identify the features. No errors just a red circle in the corner and no results.

The ObjectID field is there and is unique and not nullible.

Our basic work flow is this:  we have a view that performs a union on the layer's A/D tables so that we can always see an as is version of the data even though its being edited.  Then to give additional information to the end user another view is created utilizing the first one to join additional attributes.  I have confirmed that the first view is the one causing issues with with the ObjectID field not being presented as an option to use as the Unique Identifier when configuring the Query Layer. 

Right now I just want to focus on why any view or query layer I create will not allow me to identify.

[BirajaNayak]

Hi Joseph,

Have you checked "versioned view" option to get all edits from A and D table. This may help.

Regarding identify tool in query layer, that should work. Please contact ESRI support to look into it at your end to troubleshoot the issue.

Thanks,

Biraja