topic Setting Version of Oracle Query Layer in Geodatabase Questions

Setting Version of Oracle Query Layer

JoeJung — Sun, 12 Dec 2021 10:18:27 GMT

I am looking for a way to set the version that a tables versioned view uses when referenced inside ArcMap 10.3 with a query layer. In sql I can use the following to return records only visible to the version specified in line 1.

EXEC sde.version_util.set_current_version('user.version_name');
select * from user.sometable_evw;

When a query layer is added into ArcMap, the table of contents shows it as referencing the version set in the sde connection file. In my testing I have found that even though the layer falls under the version you may want, it is only reading rows referenced in the default version.

Does anyone know if there is another way to set the version the view is using?

Thanks,

Joe

Re: Setting Version of Oracle Query Layer

JoshuaBixby — Wed, 14 Oct 2015 16:10:06 GMT

I see the same behavior with ArcGIS 10.3.1 and SQL Server. It is arguable whether what you are experiencing is a bug, design limitation, or simply the design of query layers. As is discussed in the documentation:

What is a query layer?
A query layer is a layer or stand-alone table that is defined by a SQL query. Query layers allow both spatial and nonspatial information stored in a DBMS to be easily integrated into GIS projects within ArcMap.
...
Query layers allow ArcMap to integrate data from geodatabases as well as from DBMSs.
....

The focus or primary purpose of query layers is accessing data from DBMSes, i.e., not geodatabases. If you are using ArcGIS Desktop to access data in a geodatabase, the assumption is that you will be using the normal way of adding data into the application and not query layers. The New Query Layer dialog box uses SDE connection files to get DBMS properties, not geodatabase properties, so the version the SDE connection file is pointed to is basically ignored.

I am not defending how this works, or doesn't work, I am just explaining that what you are seeing is expected given the current implementation of query layers.

Re: Setting Version of Oracle Query Layer

JoeJung — Wed, 14 Oct 2015 20:30:43 GMT

Thanks Joshua, this is what I was afraid of.

I tend to rely heavily on query layers since I do not have SQL access to my organizations production data.

I do see a use case where one might want to interact with dbms tables and geodatabase feature classes in a versioned environment, or maybe I am the exception.

Re: Setting Version of Oracle Query Layer

JoshuaBixby — Thu, 15 Oct 2015 13:00:49 GMT

Query layers is a form of SQL access to data, so I guess I don't understand your statement regarding not having SQL access to your organizations production data. If that were true, you wouldn't be retrieving any data through query layers either.

Regarding the use case where someone wants to interact with DBMS tables and geodatabase feature classes in a versioned environment, I think that is quite a common situation. If you want to work with DBMS tables, i.e., non-geodatabase tables or feature classes, then you can use query layers to pull the data into ArcMap. If you want to work with versioned data from a geodatabase, you can use the normal add data workflows to load them into ArcMap. There is nothing that prevents someone from mixing different types of data sources in the same map document.

I may be misunderstanding your situation.

Re: Setting Version of Oracle Query Layer

JoeJung — Thu, 15 Oct 2015 13:55:34 GMT

Our IT shop creates and manages our sde connection files, so I do not have a username or password to make a database connection outside of ArcMap. By SQL access, I mean using the database outside of the esri environment.

What I meant by interact is using versioned feature classes and dbms in SQL through a query layer, to generate a table using joins, unions, groups, etc. I know how to do these things with GP tools no problem, what I am interested in seeing are these things happen dynamically while CRUD operations are being performed by other systems.

I hope I made myself clear. I appreciate the insight.