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

Question

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

Opened this issue 10 years ago · 23 comments

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.

Answer 1 · 2014-08-31T13:17:22.000Z

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.

Answer 2 · 2014-11-22T18:19:31.000Z

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 )

Answer 3 · 2015-03-18T19:47:53.000Z

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.

Answer 4 · 2016-02-26T14:13:02.000Z

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

Answer 5 · 2016-09-27T22:51:46.000Z

Thanks @miguelerm, this worked for us too :)

Answer 6 · 2017-01-11T01:41:32.000Z

Thanks @miguelerm this solution worked for me

Answer 7 · 2017-12-13T11:53:44.000Z

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.

Answer 8 · 2017-12-13T13:05:21.000Z

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" <notifications@github.com> 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 <#142 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AABDsO3MqeYxCpnvON0FZM9OU8UIO42sks5s_7rJgaJpZM4CUlW3> .

Answer 9 · 2019-03-08T15:25:13.000Z

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

Answer 10 · 2019-08-12T04:22:38.000Z

I have the same issue .

Answer 11 · 2019-09-02T18:13:54.000Z

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.

Answer 12 · 2019-11-27T11:40:43.000Z

With Dapper.Oracle this worked for me:

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

Answer 13 · 2019-11-27T12:01:18.000Z

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?

Answer 14 · 2019-11-30T19:24:06.000Z

Worked with more than 300.000 characters.

Answer 15 · 2019-12-17T12:40:46.000Z

I have the same issue . @mgravell

Answer 16 · 2020-05-04T02:28:11.000Z

@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.

Answer 17 · 2021-06-23T07:08:45.000Z

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

Answer 18 · 2021-07-25T13:07:10.000Z

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

Answer 19 · 2022-03-16T00:50:28.000Z

I have same question

Answer 20 · 2022-05-25T14:56:57.000Z

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.

Answer 21 · 2022-09-05T07:21:26.000Z

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

Answer 22 · 2023-07-22T18:18:55.000Z

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

Answer 23 · 2024-02-11T20:32:13.000Z

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