ISERROR function to flag rows that error-out in query

User_1871

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.

mtefft

Can you give an example of how you would use it?

I appreciate the frustration in tracking down the errant data that causes a query to raise an error. But I can't visualize what you are proposing here, and how it would actually be used.

User_1871

@mtefft

Here's the example from the link mentioned in my original post (https://stackoverflow.com/questions/72453564/determine-what-rows-are-causing-error-in-query):

I have an Oracle 18c table that has 15,000 rows. As a test, I'm trying to run the following query on it:

select
    --works for all rows:
    --sdo_util.to_wkbgeometry(sdo_geometry(replace(sde.st_astext(shape),'LINESTRING M','LINESTRING')))
    
    --doesn't work for all rows (the problem is caused by: SDO_UTIL.FROM_WKBGEOMETRY() ):
    sdo_util.from_wkbgeometry(sdo_util.to_wkbgeometry(sdo_geometry(replace(sde.st_astext(shape),'LINESTRING M','LINESTRING'))))
from
    my_table;

When I run that query in SQL Developer, it initially runs without errors, but that's because it's only selecting the first 50 rows.

If I try to run the query on all rows (via CTRL+END), then it throws an error:

ORA-29532: Java call terminated by uncaught Java exception: java.lang.RuntimeException: oracle.spatial.util.GeometryExceptionWithContext: Byte order can only be either BIG_ENDIAN (encoded as 0) or LITTLE_ENDIAN (encoded as 1). Found encoding 65
ORA-06512: at "MDSYS.SDO_JAVA_STP", line 68
ORA-06512: at "MDSYS.SDO_UTIL", line 6244
29532. 00000 -  "Java call terminated by uncaught Java exception: %s"
*Cause:    A Java exception or error was signaled and could not be
           resolved by the Java code.
*Action:   Modify Java code, if this behavior is not intended.

How can I determine what specific rows are causing that error? For example, in a query, it would help if we could generate a HASERROR column and populate it with 0 or 1.

Custom function:

Using a custom function, I was able to determine the following:

The problem seems to happen when a WKB geometry is 3d —and— multi-part. The to_wkbgeometry() function can't convert 3d multi-part WKB to SDO_GEOMETRY. I think that's likely an Oracle bug.

More information here: Convert 3d multi-part WKB to SDO_GEOMETRY

Summary:

It would have been nice to perform that test without needing to write a custom function. An OOTB ISERROR function would have saved some time. It would have let me easily flag the problem rows — as having an error or not. From there, I would investigate the problem rows, and determine what's different about them...and hopefully find a solution.

ISERROR(my_expression) return 0 or 1

Cheers.