Query Layer (Oracle) using SDO_BUFFER does not show(draw) geometry

[MrRock78]

Hello everyone,
I am facing some issues with the Query Layer and would appreciate it if someone could assist me in resolving this problem. I am working with an Oracle 19c database that includes the Spatial Data Module, and it contains a view displaying real-time mobile positions. My goal is to represent a security area on the map as a circle with a radius of 500 meters.
To achieve this, I attempted to add a query layer using the following SQL query:
```sql
SELECT ID, SDO_BUFFER(geom, 0.5, 0.5, 'unit=km arc_tolerance=0.5') FROM vw_position_mobile

, but it does not draw on the map. Any clue about that?

[George_Thompson]

I am not sure that you can run SQL functions (like SDO_BUFFER) in a query layer.  I do not have Oracle to test this.

You can add "Predefined discrete parameters" as shown here: https://pro.arcgis.com/en/pro-app/latest/help/mapping/layer-properties/define-parameters-in-a-query-... as a start.

If you remove the SDO_BUFFER, does the query layer work?

[AlfredBaldenweck]

Take this with a grain of salt because I've never used Oracle and the documentation  isn't super backing me up here.

You can return spatial sql at least with Microsoft SQL Server.

Idk about Oracle specifically, but for SQL Server I got a similar thing to work by manually casting the result of my buffer to geometry (or geography, depending on what you're doing) and also assigning it as the SHAPE field

//returns a 1 mile buffer for each feature

select OBJECTID,
ADM_UNIT_CD,
ADMU_NAME,
geography:: STPolyFromText(SHAPE.STAsText(), 4269).STBuffer(1609.344) AS shape
from gdb.FeatureClass

I'd start by at least assigning the buffer as the SHAPE field and see if that helps?

SELECT 
ID, 
SDO_BUFFER(geom, 0.5, 0.5, 'unit=km arc_tolerance=0.5') As shape 
FROM vw_position_mobile

The documentation that I linked above appears to not care about whether you are doing that, but Arc is a little finicky, so I'd try it just in case.