Dapper 無法寫入長字串到 CLOB 欄位

同事分享 Dapper + Oracle 遇到的問題，寫入較長字串到 CLOB 或 NCLOB 欄位時，出現「指定的引數超出有效值的範圍/Specified argument was out of the range of valid values.」錯誤。不意外地，上限落在 VarChar2/NVarChar2 的長度上限 4000：

我用 Dapper 以搭配 SQL 或 SQLite 為主，應用在 Oracle 時沒處理到這類長字串欄位，所以到現在才知道這個高懸多年的已知問題，已知 Workaround 有二：

1. 改用 DynamicParameters，DynamicParameters.Add("paramName", longString, DbType.String, size: 65536 或所需長度)
2. 以匿名物件提供參數時，以網友分享的 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.