nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Setting Version of Oracle Query Layer

JoeJung

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

Encuentra más publicaciones etiquetadas con

Accepted answers

bixb0012

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.

All comments

JoeJung

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.

bixb0012

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.

JoeJung

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.

bixb0012

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.
....

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.

Únete a la Conversación

Los miembros registrados pueden publicar, seguir actualizaciones y más. ¿Nuevo aquí? Regístrate gratis.

Enlaces Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.