Identify Fails w/ Oracle Database Views

1241
8
Jump to solution
08-01-2016 11:37 AM
Highlighted
New Contributor


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.

Reply
0 Kudos
1 Solution

Accepted Solutions
Highlighted
Regular Contributor III

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?

View solution in original post

8 Replies
Highlighted
Regular Contributor III

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?

View solution in original post

Highlighted
New Contributor

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

Reply
0 Kudos
Highlighted
New Contributor

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

Reply
0 Kudos
Highlighted
Occasional Contributor III

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

Reply
0 Kudos
Highlighted
Occasional Contributor II

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

Reply
0 Kudos
Highlighted
New Contributor

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 !

Reply
0 Kudos
Highlighted
New Contributor

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.

Reply
0 Kudos
Highlighted
Occasional Contributor III

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

Reply
0 Kudos