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
ORA-00932: inconsistent datatypes: expected - got NCLOB
However, I can insert a 4,000 character string value into the field without any issue when doing so using SQL Developer.
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.
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, 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.)
EDIT: 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();
}
APRIL 3 UPDATE:
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" ;
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();
For now, I am still pointing fingers at ODAC being buggy as it pertains to LINQ to Entities...
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:
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.
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.
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:
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]