Hi Folks,
I'm attempting to convert Microsoft SQL Server spatial data types Geometry and Geography to Oracle's SDO_GEOMETRY data type.
I want to use the well known binary (WKB) / well known text (WKT) methods to convert from SQL Server and to Oracle spatial types.
Has anyone written well known binary data to an Oracle SDO_GEOMETRY type using SDO_UTIL.FROM_WKBGEOMETRY or SDO_UTIL.FROM_WKTGEOMETRY ?
What Datastage data types must I use to contain the WKB or WKT data?
I'm interested to know if anyone has successfully performed this?
SQL Server spatial to Oracle spatial
Moderators: chulett, rschirm, roy
Re: SQL Server spatial to Oracle spatial
I'm using an Oracle Connector stage to input Well Known Text (spatial) into an Oracle column of data type SDO_GEOMETRY.
I'm using the following insert statement:-
INSERT INTO SPATIALTABLE (ROAD_CLOSURE_POLYLINE_ID,
ROAD_CLOSURE_EVENT_ID,
POLYLINE,
POLYLINETEXT,
LAST_UPDATE_DATE)
VALUES (:ROAD_CLOSURE_POLYLINE_ID,
:ROAD_CLOSURE_EVENT_ID,
SDO_UTIL.FROM_WKTGEOMETRY(:POLYLINE),
:POLYLINETEXT,
:LAST_UPDATE_DATE).
The :POLYLINE is of DataStage type LongVarChar and contains WKT (well known text - linestrings).
The POLYLINE column in the SPATIALTABLE table is of type SDO_GEOMETRY.
The SDO_UTIL.FROM_WKTGEOMETRY function will read the POLYLINE data and convert it to a SDO_GEOMETRY.
The error i'm receiving is :-
Oracle_Connector_4,1: The OCI function OCIStmtExecute returned status -1. Error code: 1,461, Error message: ORA-01461: can bind a LONG value only for insert into a LONG column. (CC_OraStatement::executeInsert, file CC_OraStatement.cpp, line 1,684)
The Oracle client on the DS engine is 11gR2.
I'm using the following insert statement:-
INSERT INTO SPATIALTABLE (ROAD_CLOSURE_POLYLINE_ID,
ROAD_CLOSURE_EVENT_ID,
POLYLINE,
POLYLINETEXT,
LAST_UPDATE_DATE)
VALUES (:ROAD_CLOSURE_POLYLINE_ID,
:ROAD_CLOSURE_EVENT_ID,
SDO_UTIL.FROM_WKTGEOMETRY(:POLYLINE),
:POLYLINETEXT,
:LAST_UPDATE_DATE).
The :POLYLINE is of DataStage type LongVarChar and contains WKT (well known text - linestrings).
The POLYLINE column in the SPATIALTABLE table is of type SDO_GEOMETRY.
The SDO_UTIL.FROM_WKTGEOMETRY function will read the POLYLINE data and convert it to a SDO_GEOMETRY.
The error i'm receiving is :-
Oracle_Connector_4,1: The OCI function OCIStmtExecute returned status -1. Error code: 1,461, Error message: ORA-01461: can bind a LONG value only for insert into a LONG column. (CC_OraStatement::executeInsert, file CC_OraStatement.cpp, line 1,684)
The Oracle client on the DS engine is 11gR2.
Re: SQL Server spatial to Oracle spatial
Just an update to my progress with this.
The Oracle spatial function SDO_UTIL.FROM_WKTGEOMETRY() can accept either varchar2 or CLOB data types.
I'm trying to use this function within an INSERT statement to populate the underlying SDO_GEOMETRY data type.
The function accepts <= 4000 characters from a CLOB.
It fails when > 4000 characters from a CLOB with the error :-
Oracle_Connector_4,1: The OCI function OCIStmtExecute returned status -1. Error code: 1,461, Error message: ORA-01461: can bind a LONG value only for insert into a LONG column. (CC_OraStatement::executeInsert, file CC_OraStatement.cpp, line 1,684)
The Oracle spatial function SDO_UTIL.FROM_WKTGEOMETRY() can accept either varchar2 or CLOB data types.
I'm trying to use this function within an INSERT statement to populate the underlying SDO_GEOMETRY data type.
The function accepts <= 4000 characters from a CLOB.
It fails when > 4000 characters from a CLOB with the error :-
Oracle_Connector_4,1: The OCI function OCIStmtExecute returned status -1. Error code: 1,461, Error message: ORA-01461: can bind a LONG value only for insert into a LONG column. (CC_OraStatement::executeInsert, file CC_OraStatement.cpp, line 1,684)
-
ray.wurlod
- Participant
- Posts: 54595
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: