Oracle 18c:
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. 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. But I don't know what row is causing the error.
Note: I'm able to write a custom function to help me identify the problem row: Find row with problem shape (SDE.ST_GEOMETRY spatial type). But it would be better if Oracle could do that OOTB.
Idea: Could Oracle error messaging be enhanced so that it tells me what row it got stuck on?
With that said, as a novice, I don't know what the best way would be to identify a given row in a error message. My table doesn't have a primary key due to limitations in the GIS software, so a primary key ID couldn't be used in an error message in my case (although my table does have an ID column with a unique index). Maybe someone who is more knowledgeable has an idea of how the problem row could be described in an error message.