ODP.NET有关spatila函数 问题

[shen521941]

我用odp.net 调用数据库 返回xml数据集
单独的只是select * from 的时候没问题
能返回数据集

但我用空间函数select * from where SDO_FILTER(...)的时候 他报错：
e.ToString () "Oracle.DataAccess.Client.OracleException ORA-03113: 通信通道的文件结束 在 Oracle.DataAccess.Client.OracleCommand.XmlHandleException(OracleException e)\r\n 在 Oracle.DataAccess.Client.OracleCommand.ExecuteXmlQuery(Boolean wantResult)\r\n 在 Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()\r\n 在 GetGeo._geo.GetSpatialData(String connnectionstring, String sql) 位置 F:\\练习\\GetGeo\\GetGeo\\_geo.cs:行号 35" string



但 我的sql语句在oracle控制台上运行的时候 不错 能返回要的到得值



请大家帮帮忙

[BlueskyWide]

听说sqlnet.ora文件里有SQLNET.AUTHENTICATION_SERVICES=(NTS) 这一行，
如果有去掉试试。


[Quote=引用楼主 shen521941 的帖子:]
我用odp.net 调用数据库 返回xml数据集
单独的只是select * from 的时候没问题
能返回数据集

但我用空间函数select * from where SDO_FILTER(...)的时候 他报错：
e.ToString () "Oracle.DataAccess.Client.OracleException ORA-03113: 通信通道的文件结束 在 Oracle.DataAccess.Client.OracleCommand.XmlHandleException(OracleException e)\r\n 在 Oracle.DataAccess.Client.OracleCommand.ExecuteXmlQuery(Boo…
[/Quote]

[BlueskyWide]

看一下：

http://www.dbanotes.net/Oracle/ORA-03113.htm

[shen521941]

to:楼上
你们说这些资料我都试过 貌似不行
行了 我也不会花100分在这问， 我都搜过了

以我得感觉 是不是odp.net不支持spatial函数查询？

[BlueskyWide]

以前碰到过这种情况，记不得怎么解决了。

服务器端字符集和客户端字符集是否匹配？

[shen521941]

[Quote=引用 4 楼 BlueskyWide 的回复:]
以前碰到过这种情况，记不得怎么解决了。

服务器端字符集和客户端字符集是否匹配？
[/Quote]

我的sql语句在 sql scratchpad里面运行就没问题
我用 XmlReader xmlreader = comm.ExecuteXmlReader();执行

[BlueskyWide]

能否试一下呢？

http://topic.csdn.net/t/20030710/20/2014176.html

[shen521941]

谢谢BlueskyWide
你的意思是？

你看看这个
http://translate.google.cn/translate?hl=zh-CN&sl=en&u=http://forums.oracle.com/forums/thread.jspa%3FthreadID%3D320597&sa=X&oi=translate&resnum=3&ct=result&prev=/search%3Fq%3DORA-03113%2Bodp.net%26hl%3Dzh-CN%26newwindow%3D1%26rlz%3D1B2GGFB_zh-CNCN285CN297

[shen521941]

http://forums.oracle.com/forums/thread.jspa?threadID=640330&tstart=-6
还是看英文版得吧

[shen521941]

不过还是找不到该怎么解决这个问题
你以前是怎么解决的？

[BlueskyWide]

可能是整个用户下存有两张损坏的表。
一个表一个表来吧。


[Quote=引用 9 楼 shen521941 的回复:]
不过还是找不到该怎么解决这个问题
你以前是怎么解决的？
[/Quote]

[shen521941]

发错地址了 是这个http://forums.oracle.com/forums/thread.jspa?threadID=320597


- -！

[shen521941]

You can access the SDO_GEOMETRY object as an XmlTextReader or XmlDocument using ODP.NET. Here's a C# example:

using System;
using System.Data;
using System.Xml;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

string cnctstr = "User ID=scott;Password=tiger;";
string sqltext = "SELECT ID, GEOM FROM TABLE";

OracleConnection con = new OracleConnection(cnctstr);
OracleCommand cmd = new OracleCommand(sqltext,con);
cmd.XmlCommandType = OracleXmlCommandType.Query;
cmd.BindByName = true;
cmd.CommandText = sqltext;
XmlTextReader reader = (XmlTextReader) cmd.ExecuteXmlReader();

// and then if you need it as a document
XmlDocument doc = new XmlDocument();
doc.Load(reader);
reader.Close();


The trick seems to be doing this in reverse and inserting/updating/filtering by a very large geometry object (eg. working with polygon that have > 400 vertices). If you use the SDO_GEOMETRY constructor in your SQL statement and the geometry linestring is > 2048 characters, it causes ORA-03113 to be returned to ODP.NET and an Oracle core dump with access violation is shown in the trace log. If anyone has any ideas outside of using OCI that'd be great.
======================
只是引用别人得 你看下 我想是的 是odp.net得问题
但是改怎么解决
===================
I've been using the XmlDocument and XmlTextReader technique for a while now to query spatial tables and it's very fast but you need to use a XmlTextReader to get good performance and skip the XmlDocument.Load() step. Never could figure out how to load a data table either. OCI was another method we tried to use but it proved very painful in .NET so we went with this Xml approach.

Inserting/updating large geometry objects, or using SDO_FILTER with a large transient polygons with hundreds of points, is a pain though when you have large linestrings of ordinates that are > 2048 characters in length. Eg.

select T.ID, T.GEOM from TABLE T where SDO_FILTER(T.GEOM, MDSYS.SDO_GEOMETRY(...some really big polygon...), 'mask=anyinteract') = 'TRUE';

This statement will fail with ORA-03113 errors and access violations/core dumps reported in the logs if the length of the SQL statement is too long, which for me maxes out at about 200 points.

There doesn't appear to be a way in ODP.NET to bind a VARRAY of numbers representing the SDO_ORDINATE_ARRAY(), or bind an SDO_GEOMETRY object itself, to your SQL statement.

Does anyone know which release Oracle plans to support objects as bind variables in ODP.NET?