How to insert string into oracle clob type with Dapper?

Question

I have a simple question about dapper with Oracle database, when I was trying to insert a large size of string into oracle clob, it throws exception says:

Specified argument was out of the range of valid values.

then I was trying to modify this part

param.Add(name: "body", value: obj.BODY, direction: ParameterDirection.Input);

I can't specify OracleDbType.Clob

What should I change to make it work?

Answer 1

Its works for me...

byte[] newvalue = System.Text.Encoding.Unicode.GetBytes(mystring);
var clob = new OracleClob(db);
clob.Write(newvalue, 0, newvalue.Length);

var parameter = new OracleDynamicParameters();    
parameter.Add("PCLOB", clob);


var command = @"Insert into MYTABLE(CLOBFIELD) values (:PCLOB)";
var t = db.Execute(command, parameter);

You could get OracleDynamicParameters class in https://gist.github.com/vijaysg/3096151

Answer 2

Solution from this thread https://github.com/DapperLib/Dapper/issues/142 :

// Install-Package Dapper.Oracle

string longString = "...";
byte[] longStringBytes = Encoding.Unicode.GetBytes(longString);

OracleClob clob = new OracleClob(this.conn);
clob.Write(longStringBytes, 0, longStringBytes.Length);

OracleDynamicParameters dynamicParams = new OracleDynamicParameters();
dynamicParams.Add("fileContent", clob, OracleMappingType.Clob, ParameterDirection.Input);

this.conn.Execute("insert into someTable (fileContent) values (:fileContent)");

Answer 3

• You can solve your problem more easily by using Oracle.DataAccess.Client.OracleCommand instead of Dapper.

• The important point you should pay attention to in this section; When creating the query, you must place the parameters in the order of the columns in the database.

   var sql = "insert into Sample_Table (col_1, col_2, col_3, col_clob) values (:col_1_param, :col_2_param, :col_3_param, :col_clob)";
  
   conn.Open();
  
   OracleCommand cmd = new OracleCommand(sql, (OracleConnection)conn);
  
       cmd.Parameters.Add("col_1_param", col_1_value);
       cmd.Parameters.Add("col_2_param", col_2_value);
       cmd.Parameters.Add("col_3_param", col_3_value);
       cmd.Parameters.Add(new OracleParameter("col_clob", col_clob_value) {                             
  OracleDbType = OracleDbType.Clob, Size = col_clob_value.Length });
  
  var result = cmd.ExecuteNonQuery();
  
  conn.Close();