我有一個包含 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()來查找問題 blob。但是，令人驚訝的是，它沒有回傳任何FALSE值。
uj5u.com熱心網友回復：
創建一個函式來包裝導致問題的呼叫并在函式中捕獲例外：
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;
uj5u.com熱心網友回復：
嘗試回圈執行（逐行會慢慢變慢，但是 - 如果你沒有更好的東西，請耐心等待 - 15.000 行并不是那么多......）。閱讀代碼中的注釋。
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;
/
uj5u.com熱心網友回復：
使用@MTO 和@LittleFoot 提供的解決方案，我能夠確定以下內容：
當 WKB 幾何體是3d -and - multi-part時，問題似乎就會發生。該to_wkbgeometry()函式無法將 3d 多部分 WKB 轉換為 SDO_GEOMETRY。我認為這可能是一個 Oracle 錯誤。
此處的更多資訊：將 3d 多部分 WKB 轉換為 SDO_GEOMETRY
測驗：
作品： 3d，單部分：
select
--Works: 3d, single-part
sdo_util.from_wkbgeometry(sdo_util.to_wkbgeometry(sdo_geometry('LINESTRING (1 2 3, 4 5 6)')))
from
dual
作品： 2d，多部分：
select
--Works: 2d, multi-part
sdo_util.to_wkbgeometry(sdo_geometry('MULTILINESTRING ((1 2, 4 5),(7 8, 0 1))'))
from
dual
不起作用： 3d，多部分：
select
--Doesn't work: 3d, multi-part
sdo_util.from_wkbgeometry(sdo_util.to_wkbgeometry(sdo_geometry('MULTILINESTRING ((1 2 3, 4 5 6),(7 8 9, 0 1 3))')))
from
dual
錯誤：
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 64
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.
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/484731.html
上一篇：如何根據特定列創建sql序列？
下一篇：空值在映射后給出錯誤值