How to insert string into oracle clob type with Dapper?

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

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)");

For out software, we just cast the large string to an object and Dapper is saving it to the CLOB column as desired:

await _dbContext.ExecuteAsync(OurInsertSqlStatement,
    new {
           record.Id,
           record.Name,
           record.CategoryId,
           LargeString = record.LargeString as object
});

• 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(); 
  

Just checked with Dapper 2.1.35 and the below works with Dynamic Parameter by mentioning the size of parameter for e.g.65563 to save 64 kb of email content

using (IDbConnection connection = new OracleConnection(_connStrBuilder.ConnectionString))
                {
                    var p = new DynamicParameters();
                   
                    p.Add("EMAILTO", emailInfo.EMAIL_TO, direction: ParameterDirection.Input, dbType: DbType.String);
                    p.Add("EMAILCC", emailInfo.EMAIL_CC, direction: ParameterDirection.Input, dbType: DbType.String);
                    p.Add("EMAILSUBJECT", emailInfo.EMAIL_SUBJECT, direction: ParameterDirection.Input, dbType: DbType.String);
                    p.Add("EMAILCONTENT", emailInfo.EMAIL_CONTENT, direction: ParameterDirection.Input, dbType: DbType.String, size:65563); //Very Large string greater than 4KB
 
                    string query = @"INSERT INTO TBL_EMAIL_INFO(EMAIL_TO,EMAIL_CC,EMAIL_SUBJECT,EMAIL_CONTENT) 
                                                                VALUES 
                                                               (:EMAILTO,:EMAILCC,:EMAILSUBJECT,:EMAILCONTENT)";
                    
                    await connection.ExecuteAsync(query, param:p);
                }