1 Reply Latest reply on Feb 14, 2013 2:32 PM by feels1

    varchar2 size limit when using parameterized query


      I have a table that has one varchar2 type column having max length=4K and and id column. It seems i can insert a row with a varchar2 of 4K length but when i try to update the varchar2 with any value that is more that 2K, using a parameterize query, i get following error:

      Exception: Oracle.DataAccess.Client.OracleException
      Message: ORA-03113: end-of-file on communication channel
      Source: Oracle Data Provider for .NET
      at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
      at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
      at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()

      But when i use a simple string query (without using any parameter)then it works fine!! Is there any work around ? I like using parameters because with simple string query i will have to worry about contents of string i am putting in e.g. esacpe single quotes, but seems like i do not have any choice .. given below are the code samples:

      // insert row . string value having length=400 (all alphabet 'A')

      string strValue = string.Empty;
      for (int j=0;j<4000;j++) strValue += "A";
      ExecuteNonQuery(connection, string.Format("insert into Tab1(Id, StrValue) values(100, '{0}')", strValue));

      // create new value having length=2001 (all alphabet 'B')
      string c = string.Empty;
      for (int j=0;j<2001;j++) newStrValue += "B";

      ExecuteNonQuery(connection, string.Format("update Tab1 set StrValue='{0}' where ID=100", strValue)); //<== this one works

      OracleCommand updateSqlCmd = new OracleCommand("update Tab1 set StrValue=:1 where ID=100");
      updatePropertySqlCmd.Parameters.Add("Value", OracleDbType.Varchar2, newStrValue, ParameterDirection.Input);
      ExecuteNonQuery(connection, updateSqlCmd); //<== throws exception

        • 1. Re: varchar2 size limit when using parameterized query
          I suspect this is due to national character set. You never mention what character set is used in your .NET program, as well as Oracle database. So I will assume in your .NET program, it is NOT plain ASCII, but highly possible UTF8.

          Byte is the native type which really represent 1 character as 1 byte, but if you use or defined your program/Windows/database as something more, then 1 character in String variable does not equals to 1 byte.

          You can use Unicode API or create a byte array to store the 2,001 character string to confirm that you are really occupying 2,001 bytes of character, and not more than 4,000 characters defined in Oracle table

          Many experience programmer/DBA often missed this point, and thinking although I defined my Windows/database as Chinese/Japanese/Poland characterset, as long as I use ASCII 0 - 127, it is using 1 byte. NO! This is NOT always the case, and varies depending on characterset, programming, OS, database.

          Oracle database's UTF8 and UTF16 character set does not compliant to most current UTF8, and UTF16 standard, which has several version. They often uses an older version of UTF8/UTF16 as they need to go through millions of testing in the product like many other, and delay the adoption of latest version

          Sorry that I do not supply any code, but you as a programmer should figure this out (character set). You will hit on this 10 - 30 years in your career, and you better become an expert now