Mismatching Geometry records (using SDO_UTIL.TO_WKTGEOMETRY()) when retrieving data using OPENQUERY from Oracle linked server to SQL Server database

Question

The overall aim is to retrieve geometric data in the form of well-known text 'WKT Geometry' along with columns 'ID' and 'REFERENCE' from our oracle database into our SQL Server database by using OPENQUERY to access the oracle linked server database

We used the SDO.UTIL_TO_WKT() function to enable us to retrieve Oracle's geometric datatype (SDO) into SQL Server in the well-known-text format (which we named the 'WKT Geometry' column). The WKT datatype is readable by T-SQL where the SDO datatype isn't.

The problem is, when creating the table in SQL Server, the rows in the WKT geometry column get disordered with the records from the ID and REFERENCE columns.

However, the records in the ID and REFERENCE columns do align properly with source data.

This is all the syntax we have tried:

--Select into tables -- 

--'Select * into': 
select * into [DATABASE].[dbo].[MY_TABLE] FROM OPENQUERY (ORACLE_SERVER, 'SELECT ID, REFERENCE, SDO_UTIL.TO_WKTGEOMETRY(SHAPE) FROM ORACLE_TABLE'); 

--'Select * into' with 'ORDER BY' in openquery: 

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

--'Select * into' with 'ORDER BY' outside and inside the open query: 

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; 

--'Select * into' with 'WHERE' clause 'REFERENCE = REFERENCE' 

select * into [DATABASE].[dbo].[MY_TABLE] FROM OPENQUERY (ORACLE_SERVER, 'SELECT ID, REFERENCE, SDO_UTIL.TO_WKTGEOMETRY(SHAPE) FROM ORACLE_TABLE WHERE REFERENCE = REFERENCE'); 

--'Select * into' with 'WHERE' clause 'REFERENCE = REFERENCE' inside openquery and out 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' Tables -- 

-- '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'); 

--'INSERT INTO' with 'ORDER BY REFERENCE' 

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'); -

-'INSERT INTO' with 'ORDER BY REFERENCE' inside and outside of open query 

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; 

--'INSERT INTO' with 'WHERE' clause 'REFERENCE = REFERENCE' 

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; 

--'INSERT INTO' with 'WHERE' clause 'REFERENCE = REFERENCE' inside openquery and out 

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; 

-- Make a Join with itself: 

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; 

--Join oracle with sql table
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;
 
--update sql table using the columns within the oracle table

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;

Here is an example that occurs across the table:

How the polygon should look like:

Reference AI/02/0145 on the original table

This is what gets inserted in instead:

Reference AI/02/0145 on the destination table

However, if just one record gets inserted then it works, but as soon as there are few more inserts, then it fails. I presume it gets confused and doesn't know where to insert the geometry, so we then created a spatial index on the geometry column on the destination table, however we can't insert plain text (what the SDO_UTIL.TO_WKTGEOMETRY(SHAPE) command retrieves) into a Geometry type column.

We have tried all the above scripts with absolutely no joy.

Has anybody come across such an issue with the GEOMETRY type?

Kind regards,

Carlos