Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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
904 views