实体框架与Oracle:无法插入VARCHAR2> 1,999个字符 [英] Entity Framework & Oracle: Cannot Insert VARCHAR2 > 1,999 Characters

问题描述

我在Oracle表中创建了一个4,000个字符的VARCHAR2字段.我正在使用LINQ to Entities和Visual Studio 2010，.NET Framework 4和 ODAC 11.2第4版和适用于Visual Studio的Oracle开发人员工具(11.2.0.3.0).当我尝试插入大于1,999个字符的字符串值时，出现以下内部异常:

I created a 4,000-character VARCHAR2 field in an Oracle table. I am inserting string values into the field using LINQ to Entities with Visual Studio 2010, .NET Framework 4, and ODAC 11.2 Release 4 and Oracle Developer Tools for Visual Studio (11.2.0.3.0). When I try to insert a string value greater than 1,999 characters, I get the following inner exception:

Oracle.DataAccess.Client.OracleException

Oracle.DataAccess.Client.OracleException

ORA-00932:数据类型不一致:预期-获得了NCLOB

ORA-00932: inconsistent datatypes: expected - got NCLOB

但是，使用SQL Developer时，我可以在字段中插入一个4,000个字符串值.

However, I can insert a 4,000 character string value into the field without any issue when doing so using SQL Developer.

有一个已知的ODAC错误(

There is a known ODAC bug (source #2) in which there is a 2,000 character limit when saving to an XMLTYPE field, but I am not saving to an XMLTYPE field. I have Oracle.DataAccess 2.112.3.0 in my GAC, and I considered updating to release 5 (11.2.0.3.20) of the aforementioned Oracle software, but "Oracle Developer Tools for Visual Studio" is the only component that appears to have been updated from release 4, and I believe that "Oracle Data Provider for .NET 4" is the component that needs updating. In my .NET project, System.Data.Entity and System.Data.OracleClient are both runtime version 4.0.30319.

无论如何，我只是想知道是否有人遇到此错误，如果是，是否找到了任何解决方案，除了上面链接的Oracle论坛线程中的一个建议使用存储过程作为解决方法的解决方案之外. Google告诉我，只有在使用XMLTYPE字段时人们才会遇到此错误，但是我不是唯一在使用VARCHAR2字段时遇到此错误的人吗?

Anyway, I am just wondering if anyone else has encountered this error, and if so, if any solution has been found - aside from the one in the Oracle forum thread that is linked above that proposes using stored procedures as a workaround. Google tells me that people are encountering this error only when working with XMLTYPE fields, but I can't be the only person who is encountering this error when working with a VARCHAR2 field, can I?

(FWIW，我也希望在上面链接的Oracle论坛线程中以用户"997340"的身份收到对我的帖子的回复.如果收到有用的回复，我一定会分享有关这方面的知识结束.)

(FWIW, I am also hoping to receive a response to my post as user "997340" in the Oracle forum thread that is linked above. If I receive a useful response, I'll be sure to share the knowledge on this end.)

编辑:如果有帮助，以下是我的代码中失败的两个块.在对第一个模块进行故障排除时，我创建了第二个模块，只是为了查看是否存在任何差异.在检查是否已插入字符串值("if"语句)以及实际插入字符串值("AddObject"语句)时，出现异常.

In case it helps, below are the two blocks in my code that are failing. I created the second block when troubleshooting the first, just to see if there was any difference. I get the exception when checking to see if the string values were already inserted (the "if" statements), and when actually inserting the string values (the "AddObject" statements).

1:

if (!(from q in db.MSG_LOG_MESSAGE where q.MESSAGE == msg select q.MESSAGE).Any())
{
  db.MSG_LOG_MESSAGE.AddObject(new MSG_LOG_MESSAGE { MESSAGE = msg });
  db.SaveChanges();
}

2:

if (!db.MSG_LOG_MESSAGE.Any(q => q.MESSAGE == msg))
{
  db.MSG_LOG_MESSAGE.AddObject(new MSG_LOG_MESSAGE { MESSAGE = msg });
  db.SaveChanges();
}

4月3日更新:

我能够跟踪从上面的第一个代码块中的"if"语句发送到Oracle的SQL.在这里:

I was able to trace the SQL that is being sent to Oracle from the "if" statement in the first code block above. Here it is:

SELECT
CASE WHEN ( EXISTS (SELECT
        1 AS "C1"
        FROM "SEC"."MSG_LOG_MESSAGE" "Extent1"
        WHERE ("Extent1"."MESSAGE" = :p__linq__0)
)) THEN 1 WHEN ( NOT EXISTS (SELECT
        1 AS "C1"
        FROM "SEC"."MSG_LOG_MESSAGE" "Extent2"
        WHERE ("Extent2"."MESSAGE" = :p__linq__0)
)) THEN 0 END AS "C1"
FROM  ( SELECT 1 FROM DUAL ) "SingleRowTable1" ;

不幸的是，我使用过的DBA没有为我提供"p_linq_0"参数的值，但是如前所述，当它超过1,999个字符时，就会发生异常. (当跟踪此SQL时，我传递了一个4,000个字符的字符串作为参数，当然发生了异常.)DBA还提到了某些SQL客户端(例如SQL Plus)的某些内容，无法处理VARCHAR2超过2,000个字符.我没有完全跟随.无论使用SQL Plus，SQL Developer还是其他任何工具，Oracle仍将查询4,000个字符的VARCHAR2字段.另外，我的幻数是1,999个字符；不是2,000个字符. DBA可能意味着参数中可以包含多少个字符存在限制吗?更重要的是，当我在SQL Developer中执行此SQL并为该参数输入4,000个字符的字符串时，它可以正常工作.所以我仍然对为什么它无法通过LINQ to Entities感到困惑.我还在程序中尝试了以下代码，以使用"msg"变量中的4,000个字符的字符串运行类似的查询，效果也很好:

Unfortunately, the DBA that I worked with did not provide me with the value of the "p_linq_0" parameter, but as previously stated, when it is over 1,999 characters, an exception occurs. (When this SQL was traced, I passed a 4,000-character string as the parameter, and of course an exception occurred.) The DBA also mentioned something about certain SQL clients - such as SQLPlus - not being able to handle VARCHAR2s over 2,000 characters. I did not entirely follow. Whether using SQLPlus, SQL Developer, or any other tool, Oracle is still going to be querying a 4,000-character VARCHAR2 field. Plus, my magic number is 1,999 characters; not 2,000 characters. Did the DBA perhaps mean there is a limitation with how many characters can be in a parameter? More importantly, when I execute this SQL in SQL Developer and I enter a 4,000-character string for the parameter, it works perfectly. So I am still utterly confused about why it is not working via LINQ to Entities. I also tried the following code in my program to run a similar query with a 4,000-character string in the "msg" variable, which worked perfectly as well:

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

...

OracleConnection conn = new OracleConnection("Data Source=[MASKED];User Id=[MASKED];Password=[MASKED]");
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT message FROM msg_log_message WHERE message = '" + msg + "'";
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
string result1 = dr.GetString(0);
conn.Dispose();

就目前而言，我仍然指责ODAC涉及越野车，因为它与LINQ to Entities ...

For now, I am still pointing fingers at ODAC being buggy as it pertains to LINQ to Entities...

推荐答案

最新的ODP.NET文档-2012年9月起的"11.2版本5生产(11.2.0.3.0)"-在实体框架的相关提示，限制和已知问题"部分，解决了问题代码块中"if"语句中的错误:

The latest ODP.NET documentation - for "11.2 Release 5 Production (11.2.0.3.0)" from September 2012 - states the following known issue under the "Entity Framework Related Tips, Limitations and Known Issues" section, which addresses the error from the "if" statements in the question's code blocks:

如果在LINQ/的WHERE子句中绑定了2,000个或更多字符的字符串或长度为4,000个或更多字节的字节数组，则会遇到"ORA-00932:不一致的数据类型"错误. ESQL查询.如果在LINQ/ESQL查询的WHERE子句中使用了映射到BLOB，CLOB，NCLOB，LONG，LONG RAW，XMLTYPE列的实体属性，则会遇到相同的错误.

An "ORA-00932 : inconsistent datatypes" error can be encountered if a string of 2,000 or more characters, or a byte array with 4,000 bytes or more in length, is bound in a WHERE clause of a LINQ/ESQL query. The same error can be encountered if an entity property that maps to a BLOB, CLOB, NCLOB, LONG, LONG RAW, XMLTYPE column is used in a WHERE clause of a LINQ/ESQL query.

较旧的ODP.NET文档-自2011年5月起发布"11.2.0.3.0版"-说明了相同的已知问题，因此显然这已经是一段时间了.

Older ODP.NET documentation - for "Release 11.2.0.3.0 Production" from May 2011 - states the same known issue, so apparently this has been a known issue for a while.

上述两个文档都没有提到在问题代码块中的"AddObject"语句遇到相同的错误，但是该问题与提到的XMLType字段的另一个已知问题非常相似:

Neither of the aforementioned documentation mentions encountering the same error from the "AddObject" statements in the question's code blocks, but that issue is very similar to another known issue for XMLType fields that is mentioned:

当尝试将长度等于或大于2,000个字符的字符串绑定到XMLType列或参数时，将遇到"ORA-00932:不一致的数据类型:预期-得到NCLOB"错误. [错误12630958]

An "ORA-00932: inconsistent datatypes: expected - got NCLOB" error will be encountered when trying to bind a string that is equal to or greater than 2,000 characters in length to an XMLType column or parameter. [Bug 12630958]

这篇关于实体框架与Oracle:无法插入VARCHAR2> 1,999个字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！