C# and Oracle Spatial Insert statement

769723

Hi

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

319958

I believe you will need to use the ODP client and not the System.Data.OracleClient library.

I have no experience with the spatial types, but I believe that you can create a UDT to do the data tunnelling from C# to Oracle and back.

here are some links that may of helpful:

http://www.oracle.com/technology/tech/windows/odpnet/index.html

http://stackoverflow.com/questions/980421/c-pass-a-user-defined-type-to-a-oracle-stored-procedure
(this one specifically has a udt-geometry example)

http://developergeeks.com/article/3/oracle-user-defined-types-in-odp-.net

769723

Hi Tanging

Thank you for your swift answer.

I am not using a UDT but a geospatial datatype.
I will take a look at the odp.

In the meanwhile I will love to hear from others if they can pinpoint my problem.

best regards,
Morten

319958

here are some more hits that may shed more light on the geo_spatial

2313905

4138634 (bit older but towards the bottom)

both contain useful links.

since the spatial is a special type you may have to jump through some hoops to get the system.data.oracleclient to work with it (also keep in mind that with the 4.0 release of c#/.net that the system.data.oracleclient is now deprecated and will no longer be maintained by ms) --> http://www.oracle.com/technology/tech/dotnet/msoc/index.html
from oracle:
"Microsoft announced its deprecation of System.Data.OracleClient, also known as Microsoft OracleClient. Microsoft OracleClient provider developers can use this opportunity to reevaluate which data provider to use for current and upcoming projects. Oracle recommends to start building new Oracle .NET applications with Oracle Data Provider for .NET (ODP.NET) and migrate existing applications to ODP.NET."
so it may not be a bad road to go down and switch to ODP

gdarling - oracle

What is the actual statement you're executing in the System.Data.OracleClient code? The one you've pasted right underneath SQL:? And that's exactly how you're executing it? No bind variables, etc? Just a hard coded literal statement and that's it?

Are you sure you're connecting as the same schema when you execute the same statement via Toad? I see ora-942 in the error stack, which is frequently a permissions issue. You're connected as MitSchema in both cases?

Can you reproduce this with a small test app that does nothing but execute that statement, similar to the code in this post for example?
1065926

System.Data.OracleClient is generally supported by Microsoft rather than Oracle if the behavior is specific to their provider. Do you see the same behavior when using Oracle's data provider? If so, it may be more appropriate to post this in the ODP.NET forum ( 3148 ), as this forms is specifically for the Oracle Developer Tools for Visual Studio


Hope it helps,
Greg

769723

Hi everyone

I have tried the odp client but that throws a ora-1031 back at me, even though I know I have the access rights correct.
Using MS VS 2008 and the app. is using .NET 2 (dont ask).

The actual c# string looks like this:
string sql = "insert into " + TableName + " (the_geom, navn, rotation, catagory, dato) values (sdo_cs.transform(sdo_geometry(2001, 4326, sdo_point_type(" + lng.ToString().Replace(',', '.') + ", " + lat.ToString().Replace(',', '.') + ", null), null, null), 25832), '" + file.Name + "', " + rotation + ", '" + Catagory + "', to_date('" + dateTime.ToString("yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS'))";

*... and how I use it:*
string connString = "Data Source=" + Server + ";Persist Security Info=True;User ID=" + User + ";Password=" + Password + ";Unicode=True";
OracleConnection con = new OracleConnection(connString);
con.Open();
OracleCommand command = new OracleCommand(sql, con);
command.ExecuteNonQuery();
con.Close();

A comment. I have done a few other apps using the same procedure as is this one without any problems. The only difference is the sdo_geometry datatype...

Edited by: Morten.M.Andersen on 2010-04-29 02:47

319958

when you did your initial test with Toad are you logging in with the same credentials as the .net application?

769723

Yep