Skip to content
Advertisement

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

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

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?

Advertisement

Answer

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

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:

Outputs:

YOUR_DATE_STRING
this is not a date

db<>fiddle here

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement