Determine what rows are causing error in query-CodePudding

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?

I tried using SDO_UTIL.VALIDATE_WKBGEOMETRY() to find the problem blobs. But, surprisingly, it didn't return any FALSE values.

CodePudding user response：

Try to do it in a loop (row-by-row which will be slow-by-slow, but - if you don't have anything better, be patient - 15.000 rows isn't that much ...). Read comments within code.

declare
  l_geom sdo_geometry;  --> I'm not sure what datatype is result of all those
                        --  geometry functions' call; I guess it is SDO_GEOMETRY.
                        --  If not, use appropriate datatype.
begin
  for cur_r in 
    (select
       id,     --> I guess there must be some kind of an ID; if not, pick any other 
               --  column which will uniquely identify that particular row
       shape
     from my_table
    ) 
  loop
    -- inner BEGIN-EXCEPTION-END block which will "capture" error on that row,
    -- but will also let the loop continue until the last row fetched by the cursor
    begin
      l_geom := sdo_util.from_wkbgeometry(sdo_util.to_wkbgeometry(sdo_geometry(replace(sde.st_astext(cur_r.shape),'LINESTRING M','LINESTRING'))));
    exception
      when others then
        dbms_output.put_line('Error on ID = ' || cur_r.id ||': '|| sqlerrm);
    end;
  end loop;
end;
/

CodePudding user response：

Create a function to wrap the call that is causing issues and catch the exception in the function:

CREATE 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;
/

Then use it in your query:

SELECT *
FROM   my_table
WHERE  test_from_wkbgeometry(
         sdo_util.to_wkbgeometry(
           sdo_geometry(replace(sde.st_astext(shape),'LINESTRING M','LINESTRING'))
         )
       ) = 0;

In later Oracle versions, you can define the function in the 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;