title: Entity Framework & Oracle: Cannot Insert VARCHAR2 > 1,999 Characters
body: <p>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 <a href="http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html" rel="nofollow noreferrer">ODAC 11.2 Release 4 and Oracle Developer Tools for Visual Studio (11.2.0.3.0)</a>. When I try to insert a string value greater than 1,999 characters, I get the following inner exception:</p>
  <blockquote>
  <p>Oracle.DataAccess.Client.OracleException</p>
  <p>ORA-00932: inconsistent datatypes: expected - got NCLOB</p>
  </blockquote>
  <p>However, I can insert a 4,000 character string value into the field without any issue when doing so using SQL Developer.</p>
  <p>There is a <a href="https://forums.oracle.com/forums/thread.jspa?messageID=10193157" rel="nofollow noreferrer">known ODAC bug</a> (<a href="https://stackoverflow.com/questions/10912650/ora-00932-inconsistent-datatypes-expected-number-got-nclob-error-when-trying">source #2</a>) 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 &quot;Oracle Developer Tools for Visual Studio&quot; is the only component that appears to have been updated from release 4, and I believe that &quot;Oracle Data Provider for .NET 4&quot; is the component that needs updating. In my .NET project, System.Data.Entity and System.Data.OracleClient are both runtime version 4.0.30319.</p>
  <p>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?</p>
  <p>(FWIW, I am also hoping to receive a response to my post as user &quot;997340&quot; 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.)</p>
  <p><strong>EDIT:</strong> 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 &quot;if&quot; statements), and when actually inserting the string values (the &quot;AddObject&quot; statements).</p>
  <p>1:</p>
  <pre><code>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();
  }
  </code></pre>
  <p>2:</p>
  <pre><code>if (!db.MSG_LOG_MESSAGE.Any(q =&gt; q.MESSAGE == msg))
  {
    db.MSG_LOG_MESSAGE.AddObject(new MSG_LOG_MESSAGE { MESSAGE = msg });
    db.SaveChanges();
  }
  </code></pre>
  <p><strong>APRIL 3 UPDATE:</strong></p>
  <p>I was able to trace the SQL that is being sent to Oracle from the &quot;if&quot; statement in the first code block above. Here it is:</p>
  <pre><code>SELECT
  CASE WHEN ( EXISTS (SELECT
          1 AS &quot;C1&quot;
          FROM &quot;SEC&quot;.&quot;MSG_LOG_MESSAGE&quot; &quot;Extent1&quot;
          WHERE (&quot;Extent1&quot;.&quot;MESSAGE&quot; = :p__linq__0)
  )) THEN 1 WHEN ( NOT EXISTS (SELECT
          1 AS &quot;C1&quot;
          FROM &quot;SEC&quot;.&quot;MSG_LOG_MESSAGE&quot; &quot;Extent2&quot;
          WHERE (&quot;Extent2&quot;.&quot;MESSAGE&quot; = :p__linq__0)
  )) THEN 0 END AS &quot;C1&quot;
  FROM  ( SELECT 1 FROM DUAL ) &quot;SingleRowTable1&quot; ;
  </code></pre>
  <p>Unfortunately, the DBA that I worked with did not provide me with the value of the &quot;p_linq_0&quot; 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 SQL<em>Plus - not being able to handle VARCHAR2s over 2,000 characters. I did not entirely follow. Whether using SQL</em>Plus, 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 &quot;msg&quot; variable, which worked perfectly as well:</p>
  <pre><code>using Oracle.DataAccess;
  using Oracle.DataAccess.Client;
  using System.Data;
  
  ...
  
  OracleConnection conn = new OracleConnection(&quot;Data Source=[MASKED];User Id=[MASKED];Password=[MASKED]&quot;);
  conn.Open();
  OracleCommand cmd = new OracleCommand();
  cmd.Connection = conn;
  cmd.CommandText = &quot;SELECT message FROM msg_log_message WHERE message = '&quot; + msg + &quot;'&quot;;
  cmd.CommandType = CommandType.Text;
  OracleDataReader dr = cmd.ExecuteReader();
  dr.Read();
  string result1 = dr.GetString(0);
  conn.Dispose();
  </code></pre>
  <p>For now, I am still pointing fingers at ODAC being buggy as it pertains to LINQ to Entities...</p>