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?

Beer

If there is a way to do this in ado.net without explicitly referencing oracle, I'm all ears. However, another option is a custom parameter - a bit like DbString. That let's you control things more. – Infrangible

@MarcGravell Could you please provide code on custom parameter? – Beer

@MarcGravell Marc, could you give comments on this https://mcmap.net/q/1466927/-dapper-amp-oracle-clob-type – Beer

To re-iterate: if there is a way of handling this nicely just using ADO.NET, I'd really love to see it; it is very vexing not being able to do things using just the abstract base types / interfaces: it kinda feels like cmd.Parameters.AddWithValue("body", obj.Body); should be enough – Infrangible

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

Cloe

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

Arabele

Answer 3

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

Woodring

Answer 4

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

Chemo

Answer 5

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

Groceries