Dapper 無法寫入長字串到 CLOB 欄位
0 |
同事分享 Dapper + Oracle 遇到的問題,寫入較長字串到 CLOB 或 NCLOB 欄位時,出現「指定的引數超出有效值的範圍/Specified argument was out of the range of valid values.」錯誤。不意外地,上限落在 VarChar2/NVarChar2 的長度上限 4000:
我用 Dapper 以搭配 SQL 或 SQLite 為主,應用在 Oracle 時沒處理到這類長字串欄位,所以到現在才知道這個高懸多年的已知問題,已知 Workaround 有二:
- 改用 DynamicParameters,DynamicParameters.Add("paramName", longString, DbType.String, size: 65536 或所需長度)
- 以匿名物件提供參數時,以網友分享的 OracleClobParameter 自訂物件取代字串傳入
我偏好後者,理由是可以保持 cn.Execute("..." new { p1 = ..., p2 = new OracleClobParameter("...") }) 的簡潔風格。而實作 ICustomQueryParameter 這招可以巧妙用在需要自訂參數轉換邏輯的地方,值得學起來。
以下是 OracleClobParameter 應用範例:
using Dapper;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace CLOBTEST
{
class Program
{
static string cs = "data source=XE;user id=...;password=...;";
static void Main(string[] args)
{
using (var cn = new OracleConnection(cs))
{
cn.Execute("DELETE FROM CLOBTEST");
cn.Execute("INSERT INTO CLOBTEST (ID,TEXT) VALUES (:id, :text)", new
{
id = 1,
text = new String('A', 4000)
});
//注意:使用 OracleClobParameter 前要先 Open()
cn.Open();
cn.Execute("INSERT INTO CLOBTEST (ID,TEXT) VALUES (:id, :text)", new
{
id = 2,
//將長於 4000 字串轉成 OracleClobParameter
text = new OracleClobParameter(new String('A', 4001))
});
Console.WriteLine(cn.Query("SELECT * FROM CLOBTEST").Count());
}
Console.ReadLine();
}
//REF: https://github.com/DapperLib/Dapper/issues/142
internal class OracleClobParameter : SqlMapper.ICustomQueryParameter
{
private readonly string value;
public OracleClobParameter(string value)
{
this.value = value;
}
public void AddParameter(IDbCommand command, string name)
{
// accesing the connection in open state.
var clob = new OracleClob(command.Connection as OracleConnection);
// It should be Unicode oracle throws an exception when
// the length is not even.
var bytes = Encoding.Unicode.GetBytes(value);
var length = Encoding.Unicode.GetByteCount(value);
int pos = 0;
int chunkSize = 1024; // Oracle does not allow large chunks.
while (pos < length)
{
chunkSize = chunkSize > (length - pos) ? chunkSize = length - pos : chunkSize;
clob.Write(bytes, pos, chunkSize);
pos += chunkSize;
}
var param = new OracleParameter(name, OracleDbType.Clob);
param.Value = clob;
command.Parameters.Add(param);
}
}
}
}
Issue about Dapper can't insert string more than 4000 chars to Oracle CLOB and the workarounds.
Comments
Be the first to post a comment