C# and Oracle Spatial Insert statement
769723Apr 28 2010 — edited Apr 29 2010Hi
I am trying to insert a sdo_point in oracle using c# but I get an annoying error (see below).
If I take the statement and execute it in, lets say Toad, I have no problem. The point is added to the table.
Please help,
Morten Andersen
Error:
System.Data.OracleClient.OracleException was unhandled
Message="
ORA-00604: error occurred at recursive SQL level 1
ORA-13236: internal error in R-tree processing: [reading node (mdrbin_mem_ins_rt)]
ORA-29400: data cartridge error
ORA-00942: table or view does not exist
ORA-06512: at \"MDSYS.SDO_IDX\", line 148
ORA-06512: at line 1"
Source="System.Data.OracleClient"
ErrorCode=-2146232008
Code=604
StackTrace:
ved System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
ved System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
ved System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
ved System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
ved GeoReferencedImages.Program.OracleSpatialInsert(String sql) i C:\Projekter\GeoReferencedImages\GeoReferencedImages\Program.cs:linje 331
ved GeoReferencedImages.Program.Main(String[] args) i C:\Projekter\GeoReferencedImages\GeoReferencedImages\Program.cs:linje 276
ved System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
ved System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
ved Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
ved System.Threading.ThreadHelper.ThreadStart_Context(Object state)
ved System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
ved System.Threading.ThreadHelper.ThreadStart()
InnerException:
SQL:
insert into MINTABEL (the_geom, navn, rotation, catagory, dato) values (sdo_cs.transform(sdo_geometry(2001, 4326, sdo_point_type(10.0364897222222, 56.4569063888889, null), null, null), 25832), 'DSC00008.JPG', 262, '', to_date('2010-04-23 12:43:07', 'YYYY-MM-DD HH24:MI:SS'))
Table script:
CREATE TABLE MitSchema.MinTabel
(
id number(20) not null,
the_geom mdsys.SDO_GEOMETRY null,
navn varchar2(32) null,
metadata varchar2(64) null,
catagory varchar2(32) null,
rotation number(3),
dato date,
CONSTRAINT MinTabel_pk PRIMARY KEY (id) using index
);
create sequence MinSequence increment by 1 start with 1 maxvalue 1.0E27 minvalue 1 nocycle cache 20 noorder;
create or replace trigger MinInsertTrigger before insert on MinTabel for each row
begin
select MinSequence.NEXTVAL into :new.id from DUAL;
end;
insert into USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
values
(
'MinTabel',
'THE_GEOM',
MDSYS.SDO_DIM_ARRAY
(
MDSYS.SDO_DIM_ELEMENT('X', 340000, 1000000, .0000001),
MDSYS.SDO_DIM_ELEMENT('Y', 5950000, 66510000, .0000001)
),
25832
);
create index MitIndex on MinTabel( THE_GEOM ) indextype is MDSYS.SPATIAL_INDEX parameters ('SDO_INDX_DIMS=2 SDO_COMMIT_INTERVAL=100');