0

I'm trying to insert large string (over 4.000 characters) into Oracle Clob column, but I keep receiving error "Internal .Net Framework Data Provider error 30". I've tried multiple things, but everything fails when String is longer than 4.000 characters. My last attempt is made from example at bottom of this site: .

Here is my code, I changed DataType Blob to Clob (It doesn't work for Blob too): 

 try
 {
   using (var conn = new OracleConnection(_conn))
   {
      conn.Open();
      OracleTransaction transaction = conn.BeginTransaction();

      using (OracleCommand cmd = new OracleCommand(SQL, conn))
      {
          cmd.Transaction = transaction;
          cmd.CommandText = "declare xx clob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;";
          cmd.Parameters.Add(new OracleParameter("tempblob", OracleType.Clob)).Direction = ParameterDirection.Output;
          cmd.ExecuteNonQuery();

          OracleLob tempLob = (OracleLob)cmd.Parameters[0].Value;
          byte[] bytes = System.Text.Encoding.Unicode.GetBytes(Large_string);
          tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
          tempLob.Write(bytes, 0, bytes.Length);
          tempLob.EndBatch();

          cmd.Parameters.Clear();
          cmd.CommandType = CommandType.StoredProcedure;

          cmd.Parameters.Add(new OracleParameter("NAME_IN", OracleType.VarChar)).Value = String_1;
          cmd.Parameters.Add(new OracleParameter("REASON_IN", OracleType.VarChar)).Value = String_2;
          cmd.Parameters.Add(new OracleParameter("ID", OracleType.Clob)).Value =tempLob; 
          cmd.ExecuteNonQuery();
      }
      transaction.Commit();
    }
  }
  catch (Exception ex)
  {
     MessageBox.Show(ex.Message);
  }

What am I missing here ?

P.S. : As stated in question title, I need solution for System.Data.OracleClient. Unfortunally I can't use DataAccess.dll (which probably solves problem,bump). For any help thanks in advance.

Lucy82
  • 654
  • 2
  • 12
  • 32
  • 1
    Does https://stackoverflow.com/questions/38322178/clob-not-inserting-a-document-more-than-4000-characters-in-stored-procedure-from help? – mjwills Jul 01 '19 at 11:52
  • @mjwills, I allready tried that solution, It didn't work for me. – Lucy82 Jul 01 '19 at 11:55
  • @mjwills, my mistake, I didn't notice comment from Russel with link of answer I haven't seen so far. Now my insert works. Apparently the only issue was on how I call Transaction within connection. Thank you very much :) – Lucy82 Jul 01 '19 at 12:09
  • Here is the answer that worked for me (accepted answer): [enter link description here](https://stackoverflow.com/questions/3557995/issues-calling-stored-procedure-from-c-sharp-with-large-clob?rq=1) – Lucy82 Jul 01 '19 at 12:12

0 Answers0