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

[caoyang1024]

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" [e-mail] 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.