Oracle view crashes ArcGIS Pro and ArcMap

Question

I have a db view in Oracle 18c (EGDB; ST_GEOMETRY):

create or replace view startpoint_vw as (
select
    objectid,
    treat(sde.st_startpoint(shape) as sde.st_geometry) as shape --https://gis.stackexchange.com/a/385767/62572
    --https://support.esri.com/en/Technical-Article/000011333
from
    my_owner.sidewalks --14,524 features
where 
    sde.st_numgeometries(shape) = 1 --The SQL will fail if there are any multi-part features. Temporary workaround: exclude multi-part features from this view.
)

Using Catalog in ArcGIS Pro 2.6.8, I want to right-click --> copy/paste that view to a FGDB (the view gets pasted as a FC).

Reason: Quickly take a snapshot of the data for geoprocessing purposes, publishing to AGOL as a FC, etc.

Problem: When I paste the view into the FGDB, ArcGIS Pro crashes about 50% of the time.

* When I re-open Pro after the crash, a FC has been created, but not all of the features were inserted. The number of features that were inserted is usually a multiple of 1000 (i.e. 2000, 4000, 8000, etc.).
* For the other 50% of the time, the view will paste as a FC without issue (all features successfully created).

I have the same problem when I paste as a FC into SDE (the same environment as the source environment). ArcGIS Pro crashes.

Has anyone had that problem when pasting views? Any ideas?

Bud · Answer

In a reply to Create NIL (zero vertex) geometry, I mention that the bug has been updated. It now suggests that we use the st_geometry constructor to convert from subtypes to supertypes. Which seems to work for converting [SDE.ST_POLYFROMTEXT] to [SDE.ST_GEOMETRY].

But that doesn't help me when converting [ST_POINT] subtype to [ST_GEOMETRY] supertype:

Bud · Answer

I also (sometimes) have the same issue when simply trying to add the view to the map. The map crashes.

Sometimes, the view crashes ArcMap 10.7.1 and ArcGIS Pro 2.6.8 — when I add the view to the map. Other times, the exact same view works fine. It doesn’t crash.

It seems to go in cycles for me. It works for a few hours or days, then it doesn’t. Then it works again.

I tried creating a new FC — by copy/pasting it in Catalog. I thought maybe there was an issue with the FC. But that didn't work either. The map still crashes.

create or replace view bc_atn_copy_vw as (
select /*+ INDEX (bc_atn_copy atn_bc_copy_idx) */ --https://support.esri.com/en/technical-article/000009658
    cast(rownum as number(38,0)) as rownum_unique_id,
    st_startpoint(shape) as shape
from
    infrastr.bc_atn_copy
where
    st_startpoint(shape) is not null --https://stackoverflow.com/a/59581129/5576771
)

Bud · Answer

I wondered if the SHAPE column was the problem, since ArcGIS Pro and ArcMap seem to be bad at handling the ST_POINT subtype (even when the TREAT() function is used). ArcGIS Pro and ArcMap seem to do a lot better when the datatype is the ST_GEOMETRY supertype.

So, instead of using the TREAT() function to convert from the ST_POINT subtype to the ST_GEOMETRY supertype, I used a low-tech method: I converted from ST_POINT to binary to ST_GEOMETRY. Alternatively, converting to text instead of binary would have been an option too.

create or replace view startpoint_vw as (
select
    objectid,
sde.st_geomfromwkb(sde.st_asbinary(sde.st_startpoint(shape)),26917) as shape
from
    my_owner.sidewalks --14,524 features
where 
    sde.st_numgeometries(shape) = 1
)

But unfortunately, ArcGIS Pro is still crashing. So I'm not sure what the problem is.

I have the same problem when building the shape from the X & Y coordinates too:

sde.st_geometry ('point (' || sde.st_x(sde.st_startpoint(shape)) || ' ' || sde.st_y(sde.st_startpoint(shape)) || ')', 26917) as shape

I also tried limiting the query to a small subset of the rows, in case there was a problematic feature somewhere, but that didn't help either. Pro still crashes.

create or replace view startpoint_vw as (
select
    objectid,
    sde.st_geomfromwkb(sde.st_asbinary(sde.st_startpoint(shape)),26917) as shape
from
    my_owner.sidewalks --14,524 features
where 
    sde.st_numgeometries(shape) = 1
and rownum <= 1000
)

I’m all out of ideas. And quite frustrated.