OOTB Oracle IFERROR function to handle rows that cause errors in a query-CodePudding

In a related post, @MTO provided a custom function for determining what rows cause errors in a query:

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. In a query, 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.

Question:

While writing a custom function works as expected, this got me thinking, are there any OOTB Oracle functions that can serve the same purpose?

For example, in Excel, we have the generic IFERROR() function. Is there anything like that for Oracle?

CodePudding user response：

In general no, however, there are out-of-the-box examples for specific things like parsing dates:

SELECT *
FROM   table_name
WHERE  -- Ignore rows that were already null
       your_date_string IS NOT NULL
AND    -- Find the rows that would throw an error when parsing them.
       TO_DATE(
         your_date_string DEFAULT NULL ON CONVERSION ERROR,
         'YYYY-MM-DD HH24:MI:SS'
       ) IS NULL;

This syntax was introduced from Oracle 12 and the default is DEFAULT ERROR ON CONVERSION ERROR but can be changed to DEFAULT NULL ON CONVERSION ERROR to allow you to find the errors.

Which, for the sample data:

CREATE TABLE table_name (your_date_string) AS
SELECT 'this is not a date' FROM DUAL UNION ALL
SELECT '2022-01-01 00:00:00' FROM DUAL UNION ALL
SELECT NULL FROM DUAL;

Outputs:

YOUR_DATE_STRING

this is not a date

YOUR_DATE_STRING
this is not a date

db<>fiddle here