Skip to Main Content

Oracle Database Discussions

ISERROR function to flag rows that error-out in query

User_1871Jun 7 2022 — edited Jun 7 2022

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.

Comments
Post Details
Added on Jun 7 2022
2 comments
270 views