Use multiple connections/databases in a query layer?

Question

I have access to two databases, each with a separate connection. The first connection is an .sde connection to a SDE 10.3.1 Oracle database, and the second connection is a .odc OLE DB connection to a third party software Oracle database. Is it possible to use both databases/connections in a query layer? I have some fairly complex non-spatial SQL queries I'd like to do using both databases; the SQL includes subqueries, group by, etc.

My guess is that the answer is no; it looks like you can only select one connection when creating a query layer. I've worked through a few alternatives, but they all seem to be lacking in one way or another:

1. Create a query layer on a table from a single connection/database, then create an in-map join to a table from the other database/connection using the layer properties in ArcMap. This is better than nothing, but is severely limited. I can't do any of the SQL querying that I need to do such as subqueries, group by, etc.

2. Use the Make Query Table geoprocessing tool, which sems to be able to reference multiple connections/databases. This has similar limitations to #1. You can enter an expression, but I believe the expression is the equivalent to the WHERE clause in SQL, which is limited. I can't do subqueries, group by, etc. here either.

3. I don't believe it's possible to create a view in one database that would reference another database.

Answer 1

Create an SDE schema in the database that holds the attribute data.

Grant connect to that SDE user on the attribute table that you want ArcGIS to see.

Mount the new SDE instance in ArcMap, it will contain the attribute table you want to join to your spatial table.

Use the standard join command between your spatial and attribute table.

Answer 2

You may want to look at dblinks. They make it possible to access a table in a remote database just as if it was local. You can then write queries that join tables from multiple databases. The query optimizer will automatically distribute the work appropriately to the various databases - like push a join between two remote tables coming from the same database, to that database. And of course you can then also create a view over that join, making the process transparent to applications.

See https://docs.oracle.com/database/121/ADMIN/ds_concepts.htm#ADMIN028 for details (follow "Database Links") for an overview.