Using ArcMap 10.4.1 I am trying to create a query layer from an Oracle table (Oracle info at bottom). The table has no geometry column, but has x and y coordinates as double and I am trying to create the geometry on the fly by returning a geometry field.
In the 'Edit Query Layer' wizard I have the following query:
SELECT MSLINK, sdo_geometry(2001,6469, sdo_point_type(CAE_DBA.NETDVD_OPEN_MAINS.FROM_X_COORD, CAE_DBA.NETDVD_OPEN_MAINS.FROM_Y_COORD, NULL), NULL,NULL) as ShapeField FROM CAE_DBA.NETDVD_OPEN_MAINS
MSLINK is my unique identifier (Long Integer, Not nullable) ArcGIS is able to discover the spatial properties for the layer and recognizes the SQL as valid. When I try to open the Attribute Table I get the following error:
"Error reading OID from Table. Reading rows has been stopped. Check that the datasource is valid. Underlying DBMS error[ORA-13226: interface not supported without a spatial index".
Do I need a spatial index on the aliased Field "ShapeField"? If so, any ideas about how to do this for a query layer?
Oracle version info: Oracle Database 12c Enterprise Edition Release 220.127.116.11.0 - 64bit Production