What are the options for accessing Oracle geodatabase data using full-blown SQL?

Question

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:

ESRI tools:

1. Database View
   • Create with ArcGIS Desktop tools or the Create Database View geoprocessing tool (or just create using DBMS tools)
2. ArcSDESQLExecute (ArcPy)
3. Query Layer
   • Create with ArcGIS Desktop tools or the Make Query Layer geoprocessing tool
4. Subqueries in an SQL expression (credit: @Brent Edwards)
   • Ex. objectid IN (SELECT objectid FROM...more complex SQL here...)

Non-ESRI tools:

1. Microsoft Access Pass-through Query

   • Create with MS Access or using VBA

2. Microsoft Query

   • Use Windows >> Start >> Find to locate it by searching MSQRY32.EXE or MSQUERY.EXE
3. SQL Plus (Credit: @Dowlers)
4. Oracle SQL Developer
5. TOAD
6. Python libraries
   • pyodbc
   • cx_oracle
7. Java (via JDBC) (credit: @Albert Godfrind)

Environment:

• ArcGIS Desktop 10.3.1
• SDE 10.3.1
• Oracle 12c
• SDE.ST_GEOMETRY

Answer 1

You said your database is Oracle 12c. But what storage type do you use for your data ? Is that the SDE binary type ? Or is it ESRI's ST_GEOMETRY types ?

If the former, then there is nothing you can do at the database level.

If the latter, then you can use ESRI's SQL extensions to write SQL queries over the data, including spatial functions (st_contains, st_intersect, st_buffer, st_distance etc).

To run those SQL queries, you can use any development environment: Java code (via JDBC), Python (via cx_oracle) ...

The pre-condition for all this is to convert your storage type to ESRI's ST_GEOMETRY type. Then again, if you do that, you could also convert to Oracle's native spatial types (SDO_GEOMETRY) and run your spatial analysis over those. Being native to the database, they are available on all platforms and typically perform more efficiently than ESRI's implementation.