在插入工作时如何更新BLOB列，错误ORA-00932 [英] How to Update a BLOB column, error ORA-00932, while Insert works

问题描述

我无法更新BLOB字段，但是插入有效，请参见下面的代码.

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

我的猜测是，它与将一个BLOB值存储在大量记录中有关，这涉及复制大数据.

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.

就我而言，我知道只会更新一条记录，但是Oracle可能认为可能需要更新几条记录.使用插入，可以保证只涉及1条记录，但不能保证总是包含更新.现在我该如何解决这个问题?

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:Where子句中的ArtNr字段是具有唯一索引的主键.

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

顺便说一句，我很担心互联网上有很多用于插入BLOB的代码示例，但是我找不到用于更新BLOB的代码示例.

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

推荐答案

我确实很高兴，但是当我阅读您的帖子时，我在想些事情.出于好奇，我尝试了一下，很惊讶这个错误确实发生了.

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:

如何使用>>更新CLOB字段中的数据.准备好的查询<<使用ODP(Oracle.DataAccess)?

事实证明，当对LOB使用update语句时，必须在参数中首先声明LOB.考虑到这一点，我遇到了与您的代码相同的错误，但这很正常:

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

只需切换参数即可.

我对这个原始问题(在本例中为同一个人)的回答表示敬意.

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

您是对的，网络上很少有关于Oracle中的BLOB更新的帮助.

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.

-编辑-

根据OP的建议，对于上面引用的同一线程，还有另一个修复程序可以防止重新排列参数的必要性.我的猜测是，如果您要更新多个LOB，这也可能会派上用场.

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.

切换BindByName属性似乎也可以解决该问题:

Switching the BindByName Property appears to also resolve the issue:

cmd.BindByName = true;

这篇关于在插入工作时如何更新BLOB列，错误ORA-00932的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！