How to copy spatial data from SQL Server 2008 to Oracle using linked server
544828Jun 15 2011 — edited Jun 16 2011Hi All,
I 'm new and hope I'm not posting in the wrong forum but...
I have created views in SQL Server with methods to convert Geography datatype to WKB and WKT.
My colleague has set up a linked server in Oracle 10g and is attempting to use SDO_UTIL.FROM_WKBGEOMETRY and SDO_UTIL.FROM_WKTGEOMETRY
to convert these to Oracles SDO_GEOMETRY datatype.
The link is working because he can select the ID field from both of my views.
If we try:
DECLARE
wkbgeom BLOB;
BEGIN
SELECT SDO_UTIL.FROM_WKBGEOMETRY(Shape) INTO wkbgeom FROM GISADMIN.BRIGADE_BDY_WKB_V@GIS c WHERE c.mi_prinx < 10;
END;
ORA-06550: line 4, column 34:
PL/SQL: ORA-00904: "SHAPE": invalid identifier
Shape being my spatial type field in the views. I changed the name
thinking this may be a reserved word in Oracle, no luck...
Same error for a similar query using WKB
Has anybody found a way to do this? Our DBA is telling us that Oracle is converting this data to LONG if that helps...