Find row with problem shape (SDE.ST_GEOMETRY spatial type)

Oracle 18c; ArcGIS 10.7.1 enterprise geodatabase:

I have a table called GCSM_HC_ANNO that has a SHAPE column (user-defined spatial type called SDE.ST_GEOMETRY; geometry type = points).

And I have a spatial query that selects GCSM_HC_ANNO points that spatially intersect a polygon row in a BOUNDARY table (ST_Intersects). The query runs without errors when only the first 50 rows are returned:

select 
    anno.objectid, 
    anno.shape
from 
    city.boundary boundary
cross join
    infrastr.gcsm_hc_anno anno
where  
    sde.st_intersects (boundary.shape, anno.shape) = 1

But when I hit CTRL+End in the resultset in SQL Developer to return all rows, I get an error:

ORA-20002: Error converting spatial reference (SHAPE2)
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 740
ORA-06512: at "SDE.ST_GEOMETRY_OPERATORS", line 2836
ORA-06512: at "SDE.ST_GEOMETRY_OPERATORS", line 3698
ORA-06512: at "SDE.ST_RELATION_OPERATORS", line 339

That tells me there is a problem with one of the SHAPES in GCSM_HC_ANNO.

Using SQL, how can I find the specific row that is causing the error?

Solution

I can create an inline PL/SQL function (or a regular function).

The function tests the shapes by intersecting against the BOUNDARY. If the intersect is successful, then the function returns "no error". But if there is a problem, then it returns "error".

That lets me flag the problem row. This technique is more useful than the original query, which throws a generic error but doesn't tell me what row is causing the problem.

with function check_shape(anno_shape sde.st_geometry, boundary_shape sde.st_geometry) return varchar2 
is
    v_test_result varchar2(10);
begin
    select
        sde.st_intersects (boundary_shape, anno_shape)
    into     
        v_test_result
    from
        dual;
    return 'no error';
exception
    when others then
        return 'error';
end;

select 
    anno.objectid, 
    anno.shape as anno_shape,
    check_shape(anno.shape, boundary.shape) as check_shape
from 
    city.boundary boundary
cross join     
    infrastr.gcsm_hc_anno anno
where 
    check_shape(anno.shape, boundary.shape) = 'error'

I'm open to ideas about improving the function.