确定哪些行导致查询错误

我有一个包含 15,000 行的 Oracle 18c 表。作为测试，我尝试对其运行以下查询：

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;

当我在 SQL Developer 中运行该查询时，它最初运行时没有错误，但这只是因为它只选择前 50 行。

如果我尝试对所有行运行查询（通过 CTRL+END），则会抛出错误：

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.

如何确定哪些特定行导致该错误？

我尝试使用SDO_UTIL.VALIDATE_WKBGEOMETRY() https://docs.oracle.com/en/database/oracle/oracle-database/18/spatl/SDO_UTIL-reference.html#GUID-5B9C48E0-197A-4A92-9D4B-1B5793B0EBEB找到问题斑点。但令人惊讶的是，它没有返回任何FALSE values.

创建一个函数来包装导致问题的调用并捕获函数中的异常：

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

然后在您的查询中使用它：

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

在更高的 Oracle 版本中，您可以在查询中定义函数：

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;