It's possible write a custom function for determining what rows cause errors in a query (https://stackoverflow.com/questions/72453564/determine-what-rows-are-causing-error-in-query).
Example:
WITH FUNCTION test_from_wkbgeometry(
v_data IN BLOB
) RETURN NUMBER
IS
temp SDO_GEOMETRY;
BEGIN
temp := sdo_util.from_wkbgeometry(v_data);
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;
SELECT *
FROM my_table
WHERE test_from_wkbgeometry(
sdo_util.to_wkbgeometry(
sdo_geometry(replace(sde.st_astext(shape),'LINESTRING M','LINESTRING'))
)
) = 0;
That custom function works well. If a function errors-out for a given row, then the custom function flags the row. That lets me find the problem rows and determine what the issue is, instead of the entire query erroring-out.
While writing a custom function works as expected, this got me thinking, it would be helpful if there were an out-of-the-box function that would serve the same purpose...making it easier to flag problem rows.
Could Oracle consider adding an OOTB ISERROR function? (or whatever's appropriate)
Thanks.