Forum Stats

  • 3,874,876 Users
  • 2,266,782 Discussions
  • 7,911,991 Comments

Discussions

ISERROR function to flag rows that error-out in query

User_1871
User_1871 Member Posts: 247 Red Ribbon

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.

User_1871
1 votes

Active · Last Updated

Comments

  • mtefft
    mtefft Member Posts: 850 Gold Badge

    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
    User_1871 Member Posts: 247 Red Ribbon
    edited Jun 21, 2022 8:30AM

    @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.