通过OPENQUERY从Oracle链接服务器同步WKT几何数据时出现错位

需求概述 #

通过OPENQUERY访问Oracle链接服务器，将Oracle数据库中的ID、REFERENCE列，以及经SDO_UTIL.TO_WKTGEOMETRY()转换为WKT格式的几何数据同步到SQL Server数据库。

问题描述 #

同步时出现两个核心问题：

1. 批量插入/创建表时，WKT几何列的行数据与ID、REFERENCE列的记录错位，但ID和REFERENCE列本身与源数据完全匹配；单条记录插入时正常。
2. 尝试在目标表几何列创建空间索引时，无法将SDO_UTIL.TO_WKTGEOMETRY()返回的纯文本插入SQL Server的Geometry类型列。

已尝试的SQL语句 #

-- Select into 系列语句
-- 基础Select * into
select * into [DATABASE].[dbo].[MY_TABLE] FROM OPENQUERY (ORACLE_SERVER, 'SELECT ID, REFERENCE, SDO_UTIL.TO_WKTGEOMETRY(SHAPE) FROM ORACLE_TABLE'); 

-- OpenQuery内加ORDER BY的Select * into
select * into [DATABASE].[dbo].[MY_TABLE] FROM OPENQUERY (ORACLE_SERVER, 'SELECT ID, REFERENCE, SDO_UTIL.TO_WKTGEOMETRY(SHAPE) FROM ORACLE_TABLE ORDER BY REFERENCE'); 

-- OpenQuery内外都加ORDER BY的Select * into
select * into [DATABASE].[dbo].[MY_TABLE] FROM OPENQUERY (ORACLE_SERVER, 'SELECT ID, REFERENCE, SDO_UTIL.TO_WKTGEOMETRY(SHAPE) FROM ORACLE_TABLE ORDER BY REFERENCE') ORDER BY REFERENCE; 

-- OpenQuery内加WHERE条件的Select * into
select * into [DATABASE].[dbo].[MY_TABLE] FROM OPENQUERY (ORACLE_SERVER, 'SELECT ID, REFERENCE, SDO_UTIL.TO_WKTGEOMETRY(SHAPE) FROM ORACLE_TABLE WHERE REFERENCE = REFERENCE'); 

-- OpenQuery内外都加WHERE条件的Select * into
select * into [DATABASE].[dbo].[MY_TABLE] FROM OPENQUERY (ORACLE_SERVER, 'SELECT ID, REFERENCE, SDO_UTIL.TO_WKTGEOMETRY(SHAPE) FROM ORACLE_TABLE WHERE REFERENCE = REFERENCE') WHERE REFERENCE = REFERENCE; 

-- INSERT INTO 系列语句
-- 基础INSERT INTO
INSERT INTO [DATABASE].[dbo].[MY_TABLE] (ID, REFERENCE, WKTGEOMETRY) SELECT ID, REFERENCE, WKTGEOMETRY FROM OPENQUERY(ORACLE_SERVER, 'SELECT ID, REFERENCE, SDO_UTIL.TO_WKTGEOMETRY(SHAPE) AS WKTGEOMETRY FROM ORACLE_TABLE'); 

-- OpenQuery内加ORDER BY的INSERT INTO
INSERT INTO [DATABASE].[dbo].[MY_TABLE] (ID, REFERENCE, WKTGEOMETRY) SELECT ID, REFERENCE, WKTGEOMETRY FROM OPENQUERY(ORACLE_SERVER, 'SELECT ID, REFERENCE, SDO_UTIL.TO_WKTGEOMETRY(SHAPE) AS WKTGEOMETRY FROM ORACLE_TABLE order by REFERENCE'); 

-- OpenQuery内外都加ORDER BY的INSERT INTO
INSERT INTO [DATABASE].[dbo].[MY_TABLE] (ID, REFERENCE, WKTGEOMETRY) SELECT ID, REFERENCE, WKTGEOMETRY FROM OPENQUERY(ORACLE_SERVER, 'SELECT ID, REFERENCE, SDO_UTIL.TO_WKTGEOMETRY(SHAPE) AS WKTGEOMETRY FROM ORACLE_TABLE ORDER BY REFERENCE') Order by Reference; 

-- OpenQuery内加WHERE条件的INSERT INTO
INSERT INTO [DATABASE].[dbo].[MY_TABLE] (ID, REFERENCE, WKTGEOMETRY) SELECT ID, REFERENCE, WKTGEOMETRY FROM OPENQUERY(ORACLE_SERVER, 'SELECT ID, REFERENCE, SDO_UTIL.TO_WKTGEOMETRY(SHAPE) AS WKTGEOMETRY FROM ORACLE_TABLE where REFERENCE = REFERENCE') Order by Reference; 

-- OpenQuery内外都加WHERE条件的INSERT INTO
INSERT INTO [DATABASE].[dbo].[MY_TABLE] (ID, REFERENCE, WKTGEOMETRY) SELECT ID, REFERENCE, WKTGEOMETRY FROM OPENQUERY(ORACLE_SERVER, 'SELECT ID, REFERENCE, SDO_UTIL.TO_WKTGEOMETRY(SHAPE) AS WKTGEOMETRY FROM ORACLE_TABLE where REFERENCE = REFERENCE') where REFERENCE = REFERENCE Order by Reference; 

-- 自连接查询
SELECT * FROM (SELECT REFERENCE, WKTGEOMETRY FROM OPENQUERY(ORACLE_SERVER, 'SELECT REFERENCE, SDO_UTIL.TO_WKTGEOMETRY(SHAPE) AS WKTGEOMETRY FROM ORACLE_TABLE') ) AS subquery1 JOIN ( SELECT REFERENCE, ID FROM OPENQUERY(ORACLE_SERVER, 'SELECT REFERENCE, ID FROM ORACLE_TABLE') ) AS subquery2 ON subquery1.REFERENCE = subquery2.REFERENCE ORDER BY subquery2.REFERENCE; 

-- Oracle与SQL Server表关联查询
SELECT A.REFERENCE, A.ID, B.WKTGEOMETRY
FROM [DATABASE].[dbo].[MY_TABLE] as A
INNER JOIN OPENQUERY(ORACLE_SERVER, 'SELECT REFERENCE, SDO_UTIL.TO_WKTGEOMETRY(SHAPE) AS WKTGEOMETRY FROM ORACLE_TABLE ') AS  B
    ON A.REFERENCE = B.REFERENCE order by REFERENCE;
 
-- 关联Oracle更新SQL Server表
UPDATE [DATABASE].[dbo].[MY_TABLE]
SET WKTGEOMETRY = oq.WKTGEOMETRY
FROM OPENQUERY(ORACLE_SERVER, 'SELECT REFERENCE, SDO_UTIL.TO_WKTGEOMETRY(SHAPE) AS WKTGEOMETRY FROM ORACLE_TABLE') oq
WHERE [DATABASE].[dbo].[MY_TABLE].REFERENCE = oq.REFERENCE;

问题示例 #

• 原Oracle表中Reference为AI/02/0145的记录对应正确的多边形几何数据
• 同步到SQL Server后，该Reference对应的多边形几何数据完全错位，与其他记录的几何信息混淆

解决方案建议 #

1. 解决WKT文本转SQL Server Geometry类型问题 #

SQL Server的Geometry类型无法直接接收纯WKT文本，必须通过geometry::STGeomFromText()或geometry::Parse()函数转换：

-- 创建目标表时指定Geometry类型列
CREATE TABLE [DATABASE].[dbo].[MY_TABLE] (
    ID INT,
    REFERENCE VARCHAR(50),
    WKTGEOMETRY GEOMETRY
);

-- 插入时显式转换WKT文本（替换4326为Oracle原数据的SRID）
INSERT INTO [DATABASE].[dbo].[MY_TABLE] (ID, REFERENCE, WKTGEOMETRY)
SELECT 
    ID,
    REFERENCE,
    geometry::STGeomFromText(WKTGEOMETRY, 4326)
FROM OPENQUERY(ORACLE_SERVER, 'SELECT ID, REFERENCE, SDO_UTIL.TO_WKTGEOMETRY(SHAPE) AS WKTGEOMETRY FROM ORACLE_TABLE');

注意：需确认Oracle原几何数据的SRID（空间参考标识符），避免空间数据坐标错误。

2. 解决批量同步时的几何数据错位问题 #

错位的核心原因通常是Oracle链接服务器对函数返回值的行映射异常，可通过以下方式规避：

方式一：Oracle端封装查询为视图

在Oracle数据库中创建包含转换后WKT字段的视图，避免在OPENQUERY中直接调用函数：

-- Oracle端创建视图
CREATE OR REPLACE VIEW ORACLE_VIEW AS
SELECT ID, REFERENCE, SDO_UTIL.TO_WKTGEOMETRY(SHAPE) AS WKTGEOMETRY
FROM ORACLE_TABLE;

SQL Server端查询视图并插入：

INSERT INTO [DATABASE].[dbo].[MY_TABLE] (ID, REFERENCE, WKTGEOMETRY)
SELECT 
    ID,
    REFERENCE,
    geometry::STGeomFromText(WKTGEOMETRY, 4326)
FROM OPENQUERY(ORACLE_SERVER, 'SELECT ID, REFERENCE, WKTGEOMETRY FROM ORACLE_VIEW');

方式二：游标逐行同步（适合小数据量）

通过游标逐行读取Oracle数据并插入SQL Server，避免批量映射错误：

DECLARE @ID INT, @REFERENCE VARCHAR(50), @WKT_TEXT VARCHAR(MAX)

DECLARE oracle_cursor CURSOR FOR
SELECT ID, REFERENCE, WKTGEOMETRY
FROM OPENQUERY(ORACLE_SERVER, 'SELECT ID, REFERENCE, SDO_UTIL.TO_WKTGEOMETRY(SHAPE) AS WKTGEOMETRY FROM ORACLE_TABLE');

OPEN oracle_cursor;
FETCH NEXT FROM oracle_cursor INTO @ID, @REFERENCE, @WKT_TEXT;

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO [DATABASE].[dbo].[MY_TABLE] (ID, REFERENCE, WKTGEOMETRY)
    VALUES (@ID, @REFERENCE, geometry::STGeomFromText(@WKT_TEXT, 4326));
    
    FETCH NEXT FROM oracle_cursor INTO @ID, @REFERENCE, @WKT_TEXT;
END;

CLOSE oracle_cursor;
DEALLOCATE oracle_cursor;

方式三：升级ODBC驱动

确保SQL Server使用的Oracle ODBC驱动为最新版本，旧版本驱动可能存在函数返回值与行记录不匹配的Bug，建议使用Oracle官方ODBC驱动。

3. 验证同步结果 #

同步完成后，通过以下语句验证数据匹配性：

-- 对比记录数
SELECT COUNT(*) FROM OPENQUERY(ORACLE_SERVER, 'SELECT * FROM ORACLE_TABLE');
SELECT COUNT(*) FROM [DATABASE].[dbo].[MY_TABLE];

-- 随机抽取记录验证几何数据
SELECT 
    o.ID, o.REFERENCE, o.WKTGEOMETRY,
    s.WKTGEOMETRY.STAsText() AS SQL_WKT
FROM OPENQUERY(ORACLE_SERVER, 'SELECT ID, REFERENCE, SDO_UTIL.TO_WKTGEOMETRY(SHAPE) AS WKTGEOMETRY FROM ORACLE_TABLE') o
JOIN [DATABASE].[dbo].[MY_TABLE] s ON o.ID = s.ID
WHERE o.ID = 123; -- 替换为实际ID

内容的提问来源于stack exchange，提问作者Carlos