How to Update a BLOB column, error ORA-00932, while Insert works

Question

I cannot update a BLOB field, but Insert works, see code below.

My guess is that it has something to do with the problem of storing one BLOB value in lots of records, involving copying large data.

In my case, I know that only one record will be updated, but Oracle might be of the opinion that potentially several records may need to be updated. With Insert, there is guaranteed only 1 record involved, but not always with Update. Now how do I get around this problem?

NB: the ArtNr field in the Where-clause is a primary key with a Unique index.

By the way, I find it worrysome that there are lots of code examples for Insert BLOB on the internet, but I could not find one for Update BLOB.

using Oracle.DataAccess.Client;//needs reference to Oracle.DataAccess.dll
using Oracle.DataAccess.Types; //OracleBlob


public static bool StoreBlobImage(OracleConnection conn, string ArtNr, byte[] bImageJpg)
{
    bool Ok = false;
#if true // this is what I need, but does not work
    string Sql = "update MyTable set Image = :Image where ArtNr = :ArtNr";
#else // this works
    string Sql = "insert into MyTable (ArtNr, Image) values (:ArtNr, :Image)";
#endif
    using (OracleCommand cmd = new OracleCommand(Sql, conn))
    {
        //cmd.Connection = conn;
        //cmd.CommandType = CommandType.Text;
        cmd.Parameters.Add("ArtNr", OracleDbType.Varchar2, 8).Value = ArtNr;
#if false // tried method 1
        cmd.Parameters.Add("Image", OracleDbType.Blob).Value = bImageJpg;
#else // now trying method 2
        OracleParameter blobParameter = new OracleParameter();
        blobParameter.OracleDbType = OracleDbType.Blob;
        blobParameter.ParameterName = "Image";
        blobParameter.Value = bImageJpg;
        blobParameter.Direction = ParameterDirection.Input;
        blobParameter.IsNullable = true;
        cmd.Parameters.Add(blobParameter);
#endif
        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();  // ORA-00932: inconsistent datatypes: expected - got BLOB
        }
        catch (Exception TheException)
        {
        }// debug breakpoint
    }
    return Ok;
}

Answer 1

I really though you were imagining things when I read your post. Out of curiousity, I tried it and was amazed that this error really does occur.

There is good news. I poked around and found this:

How can I update data in CLOB fields using a >> prepared query << with ODP (Oracle.DataAccess)?

It turns out when using an update statement with an LOB, the LOB has to be declared first in the parameters. With that in mind, I got the same error you did with your code, but this worked perfectly:

public static bool StoreBlobImage(OracleConnection conn, string ArtNr, byte[] bImageJpg)
{
    bool Ok = false;
    string Sql = "update MyTable set Image = :Image where ArtNr = :ArtNr";

    using (OracleCommand cmd = new OracleCommand(Sql, conn))
    {
        cmd.Parameters.Add("Image", OracleDbType.Blob).Value = bImageJpg;
        cmd.Parameters.Add("ArtNr", OracleDbType.Varchar2, 8).Value = ArtNr;

        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (Exception TheException)
        {
        }
    }
    return Ok;
}

Simply by switching the parameters.

I gave a kudo to the question and answer of that original question (same guy, in this case).

You are right, there is precious little on the web in the way of help for updates on BLOBs in Oracle.

Great question. I feel like I learned something today.

-- EDIT --

Per OP's suggestion, there is another fix, per the same thread referenced above, that can prevent the necessity of rearranging the parameters. My guess is this might also come in handy if you are updating multiple LOBs.

Switching the BindByName Property appears to also resolve the issue:

cmd.BindByName = true;