Failed to insert large size of string into oracle clob type.

[caoyang1024]

trafficstars

When I was trying to insert a large size of string into oracle clob type, I will see exception : Specified argument was out of the range of valid values.

[IanLedzion]

I've had a similar case with Guids stored as Oracle RAW(16). Adding a TypeHandler<> seems to work for getting the data out of the DB, but the SetValue method does not get called when inserting.

[rms81]

Same problem here. The exception is thrown by: Oracle.DataAccess.Client.OracleParameter.set_Size(Int32 value), that is called by dynamic method: ParamInfoec73d72f-a86e-4a1e-a7fe-ab4d77846ed0(IDbCommand , Object )

[miguelerm]

I solved in my project creating a custom query parameter, someting like this:

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 Oracle.DataAccess.Types.OracleClob(command.Connection as Oracle.DataAccess.Client.OracleConnection);

        // It should be Unicode oracle throws an exception when
        // the length is not even.
        var bytes = System.Text.Encoding.Unicode.GetBytes(value);
        var length = System.Text.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 Oracle.DataAccess.Client.OracleParameter(name, Oracle.DataAccess.Client.OracleDbType.Clob);
        param.Value = clob;

        command.Parameters.Add(param);
    }
}

And then using this parameter in my command:

connection.Execute("INSERT INTO MESSAGES VALUES (:id, :text)", new {id = 1, text = new OracleClobParameter("my large text") });

It's not a real solution, but works for me, :)

I hope this helps to create a real solution in a future.

[Boggin]

+1 for @miguelerm as his solution also worked for me

[rpbeier]

Thanks @miguelerm, this worked for us too :)

[warnee]

Thanks @miguelerm this solution worked for me

[waldimen]

I am using dynamic parameters and it does not work for me:

    var itemParams = new DynamicParameters(doc);
    itemParams.Add("LargeText",new OracleClobParameter(doc.LargeText));

It is working I had to change paramter name.

[mgravell]

well... what happened? an exception (and if so: exactly what)? or was the data truncated? not sent at all?

On 13 Dec 2017 11:53 a.m., "waldimen" [email protected] wrote:

I am using dynamic parameters and it does not work for me:

var itemParams = new DynamicParameters(doc);
itemParams.Add("LargeText",new OracleClobParameter(doc.LargeText));

Any ideas how to make it work ?

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/StackExchange/Dapper/issues/142#issuecomment-351369861, or mute the thread https://github.com/notifications/unsubscribe-auth/AABDsO3MqeYxCpnvON0FZM9OU8UIO42sks5s_7rJgaJpZM4CUlW3 .

[AttilaDNagy]

I have the same issue (.NET core) in March 2019. Are they planning to solve this issue?

[MetSystem]

I have the same issue .

[diegosps]

The problem still happens on version 1.60.6 with oracle managed data access core (2.19.31). Thankfully, @miguelerm solution still works with adjusted namespaces.

[wast]

With Dapper.Oracle this worked for me:

var parameters = new OracleDynamicParameters();
parameters.Add("JSONDOCUMENT", saveRequest.JsonDocument, OracleMappingType.Clob, ParameterDirection.Input);

[AttilaDNagy]

With Dapper.Oracle this worked for me:

var parameters = new OracleDynamicParameters();
parameters.Add("JSONDOCUMENT", saveRequest.JsonDocument, OracleMappingType.Clob, ParameterDirection.Input);

Was the parameter string in C# with more than 4000 characters?

[wast]

Worked with more than 300.000 characters.

[luoyanglihao]

I have the same issue . @mgravell

[NickCraver]

@luoyanglihao did you try the Dapper.Oracle approach above? Oracle has so many non-standard things happening w.r.t. .NET, I'm leaning towards just recommending that library and not changing Dapper core to work around the issues...since they're doing a great job of it.

[deepbluesky]

Usage of DynamicParameters worked for us. (Dapper version 2.0.90)

var queryParams = new DynamicParameters(new  
{  
    SYSTEMID = SystemId,  
    KEYNAME = KeyName  
});  
queryParams.Add("TOKEN", value, DbType.String, size: 65536);
_connection.Execute(UPDATE_TOKEN, queryParams);

[sunghwan2789]

Usage of DynamicParameters worked for us. (Dapper version 2.0.90)

var queryParams = new DynamicParameters(new  
{  
    SYSTEMID = SystemId,  
    KEYNAME = KeyName  
});  
queryParams.Add("TOKEN", value, DbType.String, size: 65536);
_connection.Execute(UPDATE_TOKEN, queryParams);

This works in Dapper v1.8.0 too. We can omit size param:

queryParams.Add("TOKEN", value, DbType.String);

[edobnet]

I have same question

[nkkarasu]

Same for me.

FrameWork: .Net 6.0 Dapper: 2.0.123 Oracle.ManagedDataAccess.Core: 3.21.65

System.ArgumentOutOfRangeException: Specified argument was out of the range of valid values. thrown if the data size exceeds 4Kb.

System.ArgumentOutOfRangeException: Specified argument was out of the range of valid values. at Oracle.ManagedDataAccess.Client.OracleParameter.set_Size(Int32 value) at ParamInfoebf64429-6c5c-4a94-9646-cd34dfc8e77f(IDbCommand , Object ) at Dapper.CommandDefinition.SetupCommand(IDbConnection cnn, Action2 paramReader) at Dapper.SqlMapper.TrySetupAsyncCommand(CommandDefinition command, IDbConnection cnn, Action2 paramReader) at Dapper.SqlMapper.ExecuteImplAsync(IDbConnection cnn, CommandDefinition command, Object param)

Also @sunghwan2789's solution worked for me.

[MiddleKerb]

Usage of DynamicParameters worked for us. (Dapper version 2.0.90)

var queryParams = new DynamicParameters(new  
{  
    SYSTEMID = SystemId,  
    KEYNAME = KeyName  
});  
queryParams.Add("TOKEN", value, DbType.String, size: 65536);
_connection.Execute(UPDATE_TOKEN, queryParams);

Thanks, this solution worked for me in Dapper 2.0.123 with .Net 6.0

[fsbflavio]

In fact using DynamicParameters solves the issue.

But I make use of anonymous parameters frequently, and unfortunately, using DynamicParameters does not allow me to retrieve the parameter name using the 'nameof' operator.

here is a sample:

public void SaveLog(Exception ex, string userId)
{
    var parameters = new
    {
        user = userId,
        message = ex.Message,
        stacktrace = ex.ToString(), //clob bigger than 4000. throws System.ArgumentOutOfRangeException
    };

    var sql = $@"INSERT INTO LOG (
                        ID_LOG, 
                        ID_USUARIO, 
                        STACKTRACE
                  ) VALUES (
                        SQ_ID_LOG.NEXTVAL, 
                        :{nameof(parameters.user)}, 
                        :{nameof(parameters.message)}, 
                        :{nameof(parameters.stacktrace)})";

    db.Execute(sql, parameters);
}

[Drwillard]

Update for 2024 - .NET 6/8, Dapper 2.1.x - @miguelerm solution is working for me with minimal modification -

using Dapper;
using System.Data;

namespace MyNamespace;

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 Oracle.ManagedDataAccess.Types.OracleClob(command.Connection as Oracle.ManagedDataAccess.Client.OracleConnection);


        // It should be Unicode oracle throws an exception when
        // the length is not even.
        var bytes = System.Text.Encoding.Unicode.GetBytes(value);
        var length = System.Text.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 Oracle.ManagedDataAccess.Client.OracleParameter(name, Oracle.ManagedDataAccess.Client.OracleDbType.Clob);
        param.Value = clob;

        command.Parameters.Add(param);
    }
}```