CODEWITHSUNDEEP
c#oracle-databaseodbcblob
psurikov
psurikov

Reputation: 3478

Invalid precision value error when writing BLOB to Oracle database

I have a table with BLOB field and I'm inserting a binary data into this table. Everything goes well unless I specify a binary data with more than 2000 bytes. In that case I'm getting the following error:

ERROR [HY104][ODBC Oracle Wire Protocol driver]Invalid precision value. Error in parameter 1
   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
   at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()

Is there any reason why the operation could fail with this error for BLOB data type? I tested this behaviour on both Oracle 11.2 and 12 and I see the same behavior. Here is the code I'm using to update BLOB:

public override void WriteBlob(int id, byte[] data)
{
    var cmd = GetConnection().CreateCommand();
    cmd.CommandText = "update TB_BLOBS set TB_BLOB=? where TB_ID=?";

    cmd.Parameters.Add(new OdbcParameter
    {
        DbType = DbType.Binary,
        ParameterName = "TB_BLOB",
        Value = data
    });

    cmd.Parameters.Add(new OdbcParameter
    {
        DbType = DbType.Int32,
        ParameterName = "TB_ID",
        Value = id
    });

    cmd.ExecuteNonQuery();
}

It works only when data.Length <= 2000. Is there any setting in Oracle I should know about that can make a difference?

Upvotes: 0

Views: 1405

Answers (2)

psurikov
psurikov

Reputation: 3478

I figured out why I was getting this error. Behind the scenes .NET framework uses SQL_VARBINARY type to bind my parameters. For Oracle this type is limited to 2000 bytes.

In order to fix this problem I needed to switch to SQL_LONGVARBINARY type. OdbcParameter does this automatically for parameter size > 8000 bytes. So my code potentially works in range size < 2000 and size > 8000. In order to force OdbcParameter to use SQL_LONGVARBINARY I can explicitly set large parameter size. According to documentation:

For variable-length data types, Size describes the maximum amount of data to transmit to the server

If I don't specify the Size explicitly it defaults to the actual array size and causes the error.

Upvotes: 1

Arvind Sharma
Arvind Sharma

Reputation: 77

Generally the Blob datalength limit runs into terabytes. However the datalenght limit for char, nchar etc is 2000 bytes. Are you moving data into any such field?
Another point. Check the buffer length. That could be a limiting factor

Upvotes: 0

Related Questions