In ArcGIS Dektop, I can use SQL expressions to query enterprise geodatabase data (expressions are essentially the WHERE clause in a query).
I'd like to do more than this. I want to use the full capabilities of SQL, such as group by, subqueries, st_geometry functions and PL/SQL. I'm really only interested in accessing live data in the database; not exporting the data as a copy.
What are the options for accessing Oracle geodatabase data using full-blown SQL?
Here's what I know of so far:
- Database View
- Create with ArcGIS Desktop tools or the Create Database View geoprocessing tool (or just create using DBMS tools)
- ArcSDESQLExecute (ArcPy)
- Query Layer
- Create with ArcGIS Desktop tools or the Make Query Layer geoprocessing tool
- Subqueries in an SQL expression (credit: @Brent Edwards)
- Ex. objectid IN (SELECT objectid FROM...more complex SQL here...)
Microsoft Access Pass-through Query
- Use Windows >> Start >> Find to locate it by searching MSQRY32.EXE or MSQUERY.EXE
- SQL Plus (Credit: @Dowlers)
- Oracle SQL Developer
- Python libraries
- Java (via JDBC) (credit: @Albert Godfrind)
- ArcGIS Desktop 10.3.1
- SDE 10.3.1
- Oracle 12c